CODEWITHSUNDEEP
dategoogle-sheetsgoogle-sheets-formula
Chris Moretti
Chris Moretti

Reputation: 607

Google Sheets: Automatically Increment Date by 1 Month

I am currently using a budget sheet that automatically updates date cells to the next "payment" date for a bill. As an example:

=VALUE("2019-03-08")+14*FLOOR((NOW()-VALUE("2019-03-08"))/14)

The cell starts with a date and it then updates every 14 days to the new date. This shows me a cell that has the date of the biweekly payment that is due. In the above example, the cell shows the value of "2019-03-08" until 2 weeks have passed. Once 14 days have passed, the cell will automatically upload to "2019-03-22". This automation will continue forever in 14-day increments. The date displayed in the cell will always update automatically to the next 14-day date. This helps me display the exact due date of a biweekly payment without updating the cell.

I want to do the same thing for a monthly payment, but using a day count of 30 is not very accurate. Is there a way to modify this method to let me update the date automatically to the 24th of the month, every month, on that day. So I start with 2/24/2019 and when 3/24/2019 hits, the cell updates to that date, and so on. The reason this is not accurate is that the months are not all 30 day periods. February is 28 days and some are 31 days. I want the cell to increment in 1 month periods in the same way that the above formula updates in 14-day increments. As an example: The date will display "2/24/2019" until the same day on the next month occurs, then the cell will update to "3/24/2019". This process will continue forever with 4/24/2019, 5/24/2019, etc. It will continue with the year as well, so 12/24/2019 will change automatically to 01/24/2020.

UPDATE: Updated the description with examples of how the behavior works and what I am looking for...

Upvotes: 1

Views: 6425

Answers (3)

Day Davis Waterbury
Day Davis Waterbury

Reputation: 2132

It's much simpler than this. Use the EDATE function (e.g.):

=EDATE(A1,1)

Where A1 is your starting date and 1 is the number of months you which to increment by. See https://support.google.com/docs/answer/3092974?hl=en for more details.

Upvotes: 4

player0
player0

Reputation: 1

this formula will increase the month every 24th and accounts for year change as well

=IF(DAY(TODAY())>=24,
 DATE(YEAR(TODAY()), MONTH(TODAY())+1, 24),
 DATE(YEAR(TODAY()), MONTH(TODAY()),   24))

Upvotes: 1

player0
player0

Reputation: 1

paste where you need and drag down:

=VALUE("2019-"&ROW(A3)&"-08")+14*FLOOR((NOW()-VALUE("2019-"&ROW(A3)&"-08"))/14)

Upvotes: 1

Related Questions