Forum Discussion

DataVitalizer's avatar
DataVitalizer
Icon for Super User rankSuper 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)"
             )

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
7 years ago

This measure will do it for that case, might need some testing for other use cases:

 

Note, for this measure, I created a Date1=TODAY() measure and a Date2=DATE(2020,8,19) measure.

 

Measure 4 = 
VAR __daysinMonth = DAY(EOMONTH([Date1],0))
VAR __years = DATEDIFF([Date1],[Date2],YEAR) - 1
VAR __months = IF(MONTH([Date1])<MONTH([Date2]),MONTH([Date2])-MONTH([Date1]),12-MONTH([Date1])+MONTH([Date2])-1)
VAR __days = IF(DAY([Date1])<DAY([Date2]),DAY([Date2])-DAY([Date1]),DAY([Date2])+(__daysinMonth-DAY([Date1])))
RETURN __years & " year, " & __months & " months and " & __days & " days"
DataVitalizer's avatar
DataVitalizer
Icon for Super User rankSuper User
7 years ago

Hi Greg_Deckler,

 

Thank you for your reply, the formula you shared returns the format I was looking for, but when I try to calculate the difference between two dates (date1=(2020;5;1) date2=(2020;5;2)) where the the difference is only 1 day the returned result is totally different  -1 year, 11 months and 1 days.

 

Thank you in advance.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    DataVitalizer - Yeah, I figured that would be the case. It's going to take some tweaking. I'll take a look when I have some time. It's an interesting problem.

     

    • DataVitalizer's avatar
      DataVitalizer
      Icon for Super User rankSuper User
      7 years ago

      I made some researches in the meanwhile but still can't find the right formula.

      Hoping you can help me community :)

       

      • PattemManohar's avatar
        PattemManohar
        Icon for Community Champion rankCommunity Champion
        7 years ago

        DataVitalizer A little tweaking to the Greg_Deckler formula will work for you...

         

        Please try below..

         

        DateDiff = 
VAR __daysinMonth = DAY(EOMONTH([Date1],0))
VAR __years = DATEDIFF([Date1],[Date2],YEAR) -1 
VAR __months = IF(MONTH([Date1])<=MONTH([Date2]),MONTH([Date2])-MONTH([Date1]),12-MONTH([Date1])+MONTH([Date2])-1)
VAR __days = IF(DAY([Date1])<DAY([Date2]),DAY([Date2])-DAY([Date1]),DAY([Date2])+(__daysinMonth-DAY([Date1])))
VAR __Final = SWITCH(TRUE(),
                    __years>0,__years & " year(s), " & __months & " month(s) and " & __days & " day(s)",
                    __months>0,__months & " month(s) and " & __days & " day(s)",
                    __days & " day(s)")
RETURN __Final