Nov 07, 2024 09:10 AM
I am trying to calculate the number of months between today's date and the "Test Date" field. When I use the
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.
Solved! Go to Solution.
Nov 08, 2024 10:41 AM
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.
Nov 07, 2024 11:47 AM
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.
Nov 08, 2024 06:27 AM
Hi,
Instead of TODAY(), compare with 1th day of the current month.
Nov 08, 2024 10:41 AM
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.