Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

# Date difference in Months and Days

Topic Labels: Formulas
Solved
710 4
cancel
Showing results for
Did you mean:  4 - Data Explorer

I had searched through the community and seen a post stating about the date difference of a period showing data like 2 months and 31 days or 3 months 30 days.
It is worse if it involves February as it will be calculated as 1 month 30 days.

Example as below. I’m making all the months defaulted to have 30 days, as per below formula.
DATETIME_DIFF(end,start,‘months’
)
& ’ M ’
&
(
DATETIME_DIFF(end,start,‘days’
)
-DATETIME_DIFF(end,start,‘months’
)
*30
)
& ‘D’

I would like to know if there is any way to make the difference to be more accurate like those in Excel?
I saw that there is a post regarding Switch() formula but I have no idea how to include that into this formula.
The Switch() formula is as below.

SWITCH(MONTH({start Date}),
1, 31,
2, IF(MOD(YEAR({start Date}), 400) = 0,
29,
IF(MOD(YEAR({start Date}), 100) = 0,
28,
IF(MOD(YEAR({start Date}), 4) = 0,
29,
28
)
)
),
3, 31,
4, 30,
5, 31,
6, 30,
7, 31,
8, 31,
9, 30,
10, 31,
11, 30,
12, 31)

Is there a way to calculate the difference exactly in Months and Days? Or should I forgo this method and just count by days instead?

1 Solution

Accepted Solutions  16 - Uranus

Simplifying the solution from a similar topic

would give you:

``````DATETIME_DIFF({end}, {start}, "months") & " M " &
DATETIME_DIFF(
{end},
"days"
) & " D"
``````
4 Replies 4  16 - Uranus

Simplifying the solution from a similar topic

would give you:

``````DATETIME_DIFF({end}, {start}, "months") & " M " &
DATETIME_DIFF(
{end},
"days"
) & " D"
``````  4 - Data Explorer

First, thanks for the help in offering solution. But it still comes to the same answer, just in a simplified way.

The point of 2 months and 31 days which is supposed to be 3 months 0 days are still not solved.

That 31 days not being automatically converted to months are a real headache.  16 - Uranus

For your date fields is “use same timezone” turned on? Try toggling that to see if it calculates correctly. It works on my end.  4 - Data Explorer

Ok, weird. But I tested using your formula again and it somehow does its work.
hmm… Perhaps I’ll just keep monitoring it… My brain suddenly works now.

Thanks so much for the help~ 