2

I am using Google Sheets and have 700+ rows that have an assortment of dates but the month is the same on all of them (monthly billing cycle)

each month I am having to manually go through each line and re-type the correct date... this take a long time.

Is there a way I can just select all the rows in one column and increase the month of each date by 1?

snippet of my sheet each month I have to change only the month and leave day and year the same

3

Assuming 'Date of Service' is column B, you can add column C with the following value in C3, then drag the fill handle down:

=DATE(YEAR(B3),MONTH(B3)+1,DAY(B3))

0

If it were me, this is what I'd do:

  1. Let's say the sheet with the data you posted above is called "Sheet1"; create another sheet called "Next Month" (or whatever makes sense to you).

  2. In the new sheet, put this formula in A1 (a variation on Nick's formula above):

=ArrayFormula(IF(ROW(Sheet1!B:B)=1, "Date of Service", IF (Sheet1!B:B="","",DATE(YEAR(Sheet1!B:B),MONTH(Sheet1!B:B)+1,DAY(Sheet1!B:B)))))

  1. Now, each month when you want to replace the dates in Sheet1!B:B, just select 'Next Month'!A:A, copy to clipboard (Ctrl-C), select Sheet1!B:B, and choose Edit > Paste Special > Paste values only.

The old dates and header will be replaced, and the column in 'Next Month' will auto-update again, ready for you to do the same next month, perpetually.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.