Google Sheets, increment cell by month

0
votes

I have a spreadsheet that keeps track of how old accounts are by months. Is there a way to have Google Sheets auto update a cell on the 1st of each month?

VG: An account is 78 months old and on Sept 1st it turns 79 months. I want to automatically update it on the 1st of the month so I won't have to manually add 1 to every account age cell.

4
google-sheets
Does your dataset include account start date? If it does you can just write a simple formula that calculates on the fly the age using system date. – nbayly
Because Google sheets acts almost identically to Excel, people that know Excel and look for the tag might miss this if it were only tagged as Google. Was not a forced tag. – grobot

4 Answers

1
votes

This is for Excel:

If the value is 78 on 25 August 2016 and you want the value to increment of the first of each month, then enter:

=78+MONTH(TODAY())-MONTH(DATEVALUE("8/25/2016"))+12*(YEAR(TODAY())-YEAR(DATEVALUE("8/25/2016")))
0
votes

Assuming that your dataset does include an account start date in column A, you can calculate how many whole months are between that date and system date today by using:

=DATEDIF(A2,TODAY(),"M")

Tested this in Excel but should work in Google Sheets as well.

0
votes

You could add another field, and do the calculation from the sheet:

[A1]=<start date>
[A2]=datedif(a1,today(),"M")

Otherwise, you could use a script to do this once a month, but you would need a list of cells somewhere that need to be updated. I would need to see an example of your spreadsheet layout to give you working code.

The process would be to:

  1. set a daily trigger
  2. check for the first of the month
    • if it is, go and update all the cells
    • if not, try again tomorrow
0
votes

This worked out well for me in Google Sheets
DATEDIF("<MY_STARTING_DATE>", today(), "M")
example:
DATEDIF("8/1/2021", today(), "M")