Elapsed period query

[Greg Hains]

Good morning.

I have the following custom function in my solution, but would like it modified. It will calculate the elapsed time in years, months and days between two dates. It works fine, however, i want it to not display any zero units for years and months.

For example.
It will calculate two dates and produce  "0y, 0m, 15d"   when I would like only "15d".
Also, the likes of a two dates where the result would be "2y, 0m, 3d" to just be "2y, 3d".
I do want to keep the days, even when they are zero. eg. "1y, 0d". (Even if the two compared dates are the same, "0d" is required.)

I am not sure how to add further logic into the last few lines to not insert text if those year and month values are zero.  

Could somebody point me i the right direction please?  Thanks in advance.

Greg

Elapsed.txt

[Steve Martino]

Untested...

Let ( [
    neg = Case ( date1 > date2 ; -1 ; 1 ) ;
    d1 = Case ( neg < 0 ; date2 ; date1 ) ;
    d2 = Case ( neg < 0 ; date1 ; date2 ) ;

    d = Mod ( Day ( d2 ) - Day ( d1 ) ; Day ( 
      Date ( Month ( d1 ) + 1 ; 0; Year ( d1 ) ) ) ) ;

    m = Mod ( Month ( d2 ) - Month ( d1 ) -
      ( Day ( d2 ) < Day ( d1 ) ) ; 12 ) ;

    y = Year ( d2 ) - Year ( d1 ) - ( ( Month ( d2 ) -
      ( Day ( d2 ) < Day ( d1 ) ) ) < Month ( d1 ) );
yr=case(not isempty(y); y & "y, ");

mt=case(not isempty(m); m & "m, ")

];

yr & mt & d & "d" & ¶ )

Edited June 13, 2018 by Steve Martino

[Greg Hains]

Hi Steve,

Thanks for getting back to me.

Unfortunately I couldn't get that to work - same result.
I tried adding in null text at the end of the with the Case to see if it would make a difference. Nope.

    yr=Case(not IsEmpty(y); y & "y, "; "");
    mt=Case(not IsEmpty(m); m & "m, "; "")

😕  

 

[OlgerDiekstra]

This works for me:

Let ( [
    date1 = get(CurrentDate) - 366;
    date2 = get(CurrentDate);

     neg = Case ( date1 > date2 ; -1 ; 1 ) ;
     d1 = Case ( neg < 0 ; date2 ; date1 ) ;
     d2 = Case ( neg < 0 ; date1 ; date2 ) ;
      d = Mod ( Day ( d2 ) - Day ( d1 ) ; Day (        Date ( Month ( d1 ) + 1 ; 0; Year ( d1 ) ) ) ) ;
      m = Mod ( Month ( d2 ) - Month ( d1 ) -       ( Day ( d2 ) < Day ( d1 ) ) ; 12 ) ;
      y = Year ( d2 ) - Year ( d1 ) - ( ( Month ( d2 ) -       ( Day ( d2 ) < Day ( d1 ) ) ) < Month ( d1 ) )
   ];
       If( y>0; y & "y, " ) & If( m>0; m & "m, " ) & d & "d" & ¶
)

 

[Greg Hains]

Thank you Olger - that worked a treat.