Jump to content
Sign in to follow this  
Greg Hains

Elapsed period query

Recommended Posts

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

Share this post


Link to post
Share on other sites
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 by Steve Martino

Share this post


Link to post
Share on other sites

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, "; "")

😕  

 

Share this post


Link to post
Share on other sites

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" & ¶
)

 

Share this post


Link to post
Share on other sites

Thank you Olger - that worked a treat. :)

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.