Google Sheets Increase month by 1 but leave everything else the same

Question

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?

Answer 1

Use EDATE function

Set the first value in the cell to a specific date. Like 1/1/2020

Second cell set this formula:

=EDATE(FirstDate,1)

Drag the value above to other cells.

Answer 2

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))

Answer 3

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)))))

3. 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.