cancel
Showing results for
Did you mean:
Regular Visitor

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

Here are the results

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

Highlighted
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)"
)```

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

Proud to be a Datanaut !

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

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

@othy_bi  Finally !!

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

Proud to be a Datanaut !