Help

Count number of months from Start Date to today

Topic Labels: Formulas
Solved
Jump to Solution
3310 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Sharon_Visser
7 - App Architect
7 - App Architect

Hi, I am trying to count the number of months from the Start Date till today.  The actual day of the 'Start Date' doesn't matter.  For example, if 'Start Date' is 9/20/23 and today is 12/11/23, I want to count the number of months that have passed: Sept, Oct, Nov, Dec = 4.  I have tried this formula:

 
DATETIME_DIFF(TODAY(),{Start Date},'months')
 
but, I know that doesn't work. I have been searching the community and haven't found the right solution.  
Any help would be appreciated.
1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

Airtable sometimes misinterprets dates, so this might be better.

DATETIME_DIFF(
  DATETIME_PARSE(DATETIME_FORMAT(TODAY(),"YYYY-MM"),"YYYY-MM-01"),
  DATETIME_PARSE(DATETIME_FORMAT({Date},"YYYY-MM"),"YYYY-MM-01"),
  "month"
)

See Solution in Thread

7 Replies 7
Sho
11 - Venus
11 - Venus

Hi @Sharon_Visser,

I think this is better.

IF({Start Date},MONTH(TODAY())-MONTH({Start Date}))

 

Hi @Sho , I tried this and it's giving me an answer of 3 instead of 4.  I think it's taking into account the days, and not just looking at months.  I'm assuming someone worked even 1 day each month between the Start and today, so then I need to count the month.  Thank you. 

Sho
11 - Venus
11 - Venus

In this case, if the "Stat date" input means one month, so +1 is sufficient.

IF(
  {Start Date},
  MONTH(TODAY())-MONTH({Start Date})+1
)

 

Hi again, I do appreciate your help, but this version only seems to work within the same year.  As soon as it crosses a year boundary it's not right.  If Start Date = '9/20/23', and Today is '1/10/24', the result is -7 rather than 5 (Sept, Oct, Nov, Dec, Jan).  I made test fields for 'Test Start', and 'Test Today' so I can plug in different values.  My formula is: 

IF({Test Start},MONTH({Test Today})-MONTH({Test Start})+1)
 
Thank you.
Sho
11 - Venus
11 - Venus

Sorry, it was a bad idea.

I should have used DIFF properly.

 

DATETIME_DIFF(
  DATETIME_FORMAT(TODAY(), "YYYY-MM"),
  DATETIME_FORMAT({Date}, "YYYY-MM"),
  "month"
)

 

Sho
11 - Venus
11 - Venus

Airtable sometimes misinterprets dates, so this might be better.

DATETIME_DIFF(
  DATETIME_PARSE(DATETIME_FORMAT(TODAY(),"YYYY-MM"),"YYYY-MM-01"),
  DATETIME_PARSE(DATETIME_FORMAT({Date},"YYYY-MM"),"YYYY-MM-01"),
  "month"
)
Sharon_Visser
7 - App Architect
7 - App Architect

Thank you, the solution using DATETIME_DIFF and DATETIME_PARSE works - though I had to add "+1" to the end of the formula.  I did some testing crossing year boundaries and it works.  This is a really helpful formula, I appreciate your help!