Date difference in Months and Days

[Wind]

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?

[Kamille_Parks · Accepted solution]

Simplifying the solution from a similar topic

Calculate details for a coffin nameplate (# Years, # Months, # Days) Formulas

Hi, I need to calculate the age of a Deceased. I assume a formula is needed. I have Date of Birth and Date of Death to work with and I need the know the age in Years, Months and Days. Any help would be greatly appreciated.

would give you:

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

[Wind]

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.

[Kamille_Parks]

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

[Wind]

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~