# 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 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 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 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 on other sites

Thank you Olger - that worked a treat.

## 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.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

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

×

×
×
• Create New...