Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Count number of months from Start Date to today

Topic Labels: Formulas
Solved
Jump to Solution
3823 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!