Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Date difference in Months and Days

Topic Labels: Formulas
Solved
Jump to Solution
2580 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Wind
4 - Data Explorer
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.
image

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
Kamille_Parks
16 - Uranus
16 - Uranus

Simplifying the solution from a similar topic

would give you:

DATETIME_DIFF({end}, {start}, "months") & " M " & 
DATETIME_DIFF(
   {end}, 
   DATEADD({start}, DATETIME_DIFF({end}, {start}, "months"), "months"), 
   "days"
) & " D"

See Solution in Thread

4 Replies 4
Kamille_Parks
16 - Uranus
16 - Uranus

Simplifying the solution from a similar topic

would give you:

DATETIME_DIFF({end}, {start}, "months") & " M " & 
DATETIME_DIFF(
   {end}, 
   DATEADD({start}, DATETIME_DIFF({end}, {start}, "months"), "months"), 
   "days"
) & " D"
Wind
4 - Data Explorer
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.

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

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~