Skip to main content

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. 

 

 

 

 

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.






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

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

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. 

 

Reply


Terms & Conditions