cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
othy_bi Regular Visitor
Regular Visitor

Re: Calculating the difference between two dates in Years, Months, and Days

Here are the results

2018-11-13_155145.png

FYI: The date format used in these examples is dd/mm/yyyy

Super User
Super User

Re: Calculating the difference between two dates in Years, Months, and Days

@othy_bi Oh !! Ok, please try this (Just added two more conditions in the SWITCH statement)

 

Hopefully, this should fix all your test cases :-) Fingers Crossed !!

 

Final = 
VAR _Years = FLOOR(YEARFRAC([Date1],[Date2],4),1)
VAR _Months = FLOOR(MOD(YEARFRAC([Date1],[Date2],4),1) * 12.0,1)
VAR _daysinMonth = DAY(EOMONTH([Date1],0))
VAR _days = IF(DAY([Date1])<DAY([Date2]),DAY([Date2])-DAY([Date1]),DAY([Date2])+(_daysinMonth-DAY([Date1])))
VAR _daysFinal =  SWITCH(TRUE(),DAY(EOMONTH([Date1],0)) = _days || DAY(EOMONTH([Date2],0)) = _days,0,_days)

RETURN SWITCH(TRUE(),
             _Years>0 && _Months = 0 && _daysFinal = 0,_Years & " year(s)",
             _Years>0 && _Months>0 && _daysFinal = 0, _Years & " year(s), " & _Months & " month(s) ",
             _Years>0 && _Months >0 && _daysFinal >0, _Years & " year(s), " & _Months & " month(s) " & _daysFinal & " day(s)",
             _Years>0 && _Months = 0 && _daysFinal >0, _Years & " year(s), " & _daysFinal & " day(s)",
             _Months>0 && _daysFinal = 0, _Months & " month(s) ",
             _Months>0 && _daysFinal >0, _Months & " month(s) " & _daysFinal & " day(s)",
             _daysFinal & " day(s)"
             )

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





othy_bi Regular Visitor
Regular Visitor

Re: Calculating the difference between two dates in Years, Months, and Days

I just tried the formula the suggested formula using other dates and they are all fixed.

I can say that this formula finally meets what I have been looking for.

 

Thank you @PattemManohar @Greg_Deckler 

Super User
Super User

Re: Calculating the difference between two dates in Years, Months, and Days

@othy_bi Smiley Very Happy Finally !!



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !