Skip to main content

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?

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"

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.

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.

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~

Reply


Terms & Conditions