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

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?

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.

• Easy and simple solution. Dec 20 '20 at 13:27

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

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.