CODEWITHSUNDEEP
google-sheets
grobot
grobot

Reputation: 51

Google Sheets, increment cell by month

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.

Upvotes: 0

Views: 6424

Answers (4)

distantkey
distantkey

Reputation: 11

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

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

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

Upvotes: 1

HardScale
HardScale

Reputation: 1021

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

Upvotes: 0

nbayly
nbayly

Reputation: 2167

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.

Upvotes: 0

Related Questions