DATETIME_DIFF gives wrong number of months

[aylapois]

I am trying to calculate the number of months between today's date and the "Test Date" field. When I use the 

DATETIME_DIFF formula it does not calculate it correctly for all values. 

In the Test copy column I have this formula:

DATETIME_DIFF({Test Date}, TODAY(), "months")

It calculates some of the values correctly but then other ones wrong and I am uncertain how else to do this calculation in Airtable. 

What I need is that if todays date is Nov 7, 2024 then any dates in Nov 2024 would give a value of 0, any dates in October 2024 would show -1 but you can see the dates on October 16 and October 30 show a value of 0. You can see this issue also on the last row of the screen shot where the date of Jan 2, 2024 should be 2 but gives a value of 1. 

[Jeff_Hladek]

Someone else correct me if I'm wrong, but I believe this is happening because DATETIME_DIFF is using months as a unit and is basically just counting in 30 day intervals. So October 16th is less than 1 month from today. Here is how I would get around this.

First just get the month number for the Test Date and of Today and find the difference.

Month({Test Date}) - Month(Today())

However, this doesnt take into account if the dates are in different years. So this formula considers 10/2024 and 10/2023 as both 1 month difference from Todays date.

So then I would add some logic to consider the difference in year and then multiply it by 12 to get 

Year({Test Date})-Year(Today())*12

You could do this in two fields and then Sum the fields together, or you could do this all in one formula.

(MONTH({Test Date}) - MONTH(TODAY())) + ((YEAR({Test Date}) - YEAR(TODAY())) * 12)

I did some testing and it worked in every situation I tested it in.

[Alexey_Gusev]

Hi,
Instead of TODAY(), compare with 1th day of the current month. 

DATETIME_FORMAT(TODAY(),'YYYY-MM-01')
Do the same for date field.

[aylapois · Accepted solution]

Yes that is what I was able to determine is that DATETIME_DIFF just appears to count 30 day intervals and not actually months. 

In the end I did this which worked exactly as I needed it. 

DATETIME_DIFF(

  DATETIME_PARSE(DATETIME_FORMAT(SET_TIMEZONE({Test Date}, "America/Vancouver"),"YYYY-MM"),"YYYY-MM-01"),

  DATETIME_PARSE(DATETIME_FORMAT(TODAY(),"YYYY-MM"),"YYYY-MM-01"), 

  "month"

) + 1

I had an additional issue due to the timezone of the values in the Test Date column as they are pulled in through an API so I had to wrap them in the SET_TIMEZONE function or else some dates at the end of the month and a time after 5pm would show up as the next month. The SET_TIMEZONE function fixed this issue.