Count number of expenses by month

[Sharon_Visser]

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.

[Sho]

Hi @Sharon_Visser,

I think this is better.

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

[Sharon_Visser]

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]

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

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

[Sharon_Visser]

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]

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 · Accepted solution]

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]

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!