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


Use EDATE function

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

Second cell set this formula:


Drag the value above to other cells.

  • Easy and simple solution.
    – llanfair
    Dec 20, 2020 at 13:27
  • Doesn't work for crossing year. Dec-15, next one resets to Jan-15.
    – Raymond
    Mar 9, 2022 at 18:45

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



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 and acknowledge you have read our privacy policy.

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