Forum Discussion

DataVitalizer's avatar
DataVitalizer
Super User
7 years ago
Solved

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

Hi Community, I am working on a report where I have to calculate the difference between two dates (a specific date, and today) then show the resulat in the following format 00years - 00months - 00da...
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    DataVitalizer 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)"
             )

DataVitalizer's avatar
DataVitalizer
Super User
7 years ago

Here are the results



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

PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

DataVitalizer 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)"
             )

  • DataVitalizer's avatar
    DataVitalizer
    Super User
    7 years ago

    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 

  • ICRdatalover's avatar
    ICRdatalover
    Advocate I
    7 years ago

    Same problem here, same user case. Thanks PattemManohar and Greg_Deckler for the help, still working for me and you save me a couple of hours!!

     

    Kind regards 

     

    ICR

  • anshulgrover7's avatar
    anshulgrover7
    Regular Visitor
    3 years ago

    Hi, Will this work if Date2= Table[ColumnDate]. I am trying this but no luck 😞

  • anshulgrover7's avatar
    anshulgrover7
    Regular Visitor
    3 years ago

    Hey, Are Date1, Date2, and Date Diff measures or calculated columns? The images above show like its a calculated column. Please confirm, need this solution to solve another problem. Thanks