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

[Anonymous]

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

[Greg_Deckler]

@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]

@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]

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

[Greg_Deckler · Accepted solution]

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