Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculating the difference between Two dates & returning Years, Months and Days

Hi,

I am trying to work out how to show the Years (Y's), months (M's) and days (D's) between two dates.

I have found an old thread that and used the last suggestion, created by Greg_Deckler and PattemManohar , as the OP said that worked for them.

 

The Syntax has worked for me to an extent, the issue is that it is only showing the largest value ( so if there are Y's,M's & D's left between the dates then it only shows the Y's. If there are M's & D's left between the dates then it is only showing the M's) 

 

What I need is for the Y's,M's & D's to show.

 

I think the issue is the RETURN SWITCH, but I dont know enough to know where the issue truely lies.

 

 

 

Time to Next Calibration = 
VAR Years = FLOOR(YEARFRAC([A036_LAST_CALIBRATED],[A037_NEXT_CALIBRATION],4),1)

VAR Months = FLOOR(MOD(YEARFRAC([A036_LAST_CALIBRATED],[A037_NEXT_CALIBRATION],4),1) * 12.0,1)

VAR daysinMonth = DAY(EOMONTH([A036_LAST_CALIBRATED],0))

VAR Days = IF(DAY([A036_LAST_CALIBRATED]) <DAY([A037_NEXT_CALIBRATION]), DAY([A037_NEXT_CALIBRATION]) - DAY([A036_LAST_CALIBRATED]), DAY([A037_NEXT_CALIBRATION]) + (daysinMonth-DAY([A036_LAST_CALIBRATED])))

VAR daysFinal = SWITCH(TRUE(),DAY(EOMONTH([A036_LAST_CALIBRATED],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)"
    )

 

 

 

 Any help would be appriciated.

 

Thanks

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    Anonymous Try:

    Time to Next Calibration = 
VAR Years = FLOOR(YEARFRAC([A036_LAST_CALIBRATED],[A037_NEXT_CALIBRATION],4),1)

VAR Months = FLOOR(MOD(YEARFRAC([A036_LAST_CALIBRATED],[A037_NEXT_CALIBRATION],4),1) * 12.0,1)

VAR daysinMonth = DAY(EOMONTH([A036_LAST_CALIBRATED],0))

VAR Days = IF(DAY([A036_LAST_CALIBRATED]) <DAY([A037_NEXT_CALIBRATION]), DAY([A037_NEXT_CALIBRATION]) - DAY([A036_LAST_CALIBRATED]), DAY([A037_NEXT_CALIBRATION]) + (daysinMonth-DAY([A036_LAST_CALIBRATED])))

VAR daysFinal = SWITCH(TRUE(),DAY(EOMONTH([A036_LAST_CALIBRATED],0)) = Days,0,Days)

RETURN 
  SWITCH(TRUE(),
    ISBLANK(Years) && ISBLANK(Months) && ISBLANK(Days), BLANK(),
    Years & " year, " & Months & " months and " & Days & " days"
  )

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous Maybe try the first suggestion in your case, that one will always return years months and days:

    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"
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Greg_Deckler 

      Thanks for responding.

      I have tried this and I receive a lot of errors.

       

       

      Do you have any other sugestions?

       

      Thansk

      Tom

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        I think I might be close to figuring it out.

        I have tried the below (using the first syntax of variables with the second syntax return)

         

        Time to Next Calibration = 
VAR Years = FLOOR(YEARFRAC([A036_LAST_CALIBRATED],[A037_NEXT_CALIBRATION],4),1)

VAR Months = FLOOR(MOD(YEARFRAC([A036_LAST_CALIBRATED],[A037_NEXT_CALIBRATION],4),1) * 12.0,1)

VAR daysinMonth = DAY(EOMONTH([A036_LAST_CALIBRATED],0))

VAR Days = IF(DAY([A036_LAST_CALIBRATED]) <DAY([A037_NEXT_CALIBRATION]), DAY([A037_NEXT_CALIBRATION]) - DAY([A036_LAST_CALIBRATED]), DAY([A037_NEXT_CALIBRATION]) + (daysinMonth-DAY([A036_LAST_CALIBRATED])))

VAR daysFinal = SWITCH(TRUE(),DAY(EOMONTH([A036_LAST_CALIBRATED],0)) = Days,0,Days)

RETURN Years & " year, " & Months & " months and " & Days & " days"

         and I receive the following

         

        the only issue is when there is no value it just shows "year,month and days" where i would need everything to be either zero if there is no value or show a minus value to show how many days over the deadline.

         

        Thanks

        Tom