1 Reply Latest reply on Apr 14, 2015 11:28 AM by TSGal

# Date Calculation (with a case statement)

### Title

Date Calculation (with a case statement)

### Post

I have two date fields in my staff record database.

pd_EngagementDate

pd_TerminationDate

Pretty self explanatory what that are :)

I currently use an un-stored calculation, returned as text,  to give me the length of their service and present it in a friendly format of xx Years, xx Months and xx days based on the current date date

GetAsText ( Year ( Get ( CurrentDate ) )  - Year ( PD_Engagement Date) - If ( Get ( CurrentDate ) <  Date ( Month ( PD_Engagement Date) ; Day ( PD_Engagement Date) ; Year ( Get ( CurrentDate ) ) ) ; 1 ; 0 ) ) & " Years, " & GetAsText ( Mod ( Month ( Get ( CurrentDate ) ) - Month ( PD_Engagement Date) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day ( PD_Engagement Date) ; 1 ; 0 ) ; 12 ) ) & " Months, " & GetAsText ( Day ( Get ( CurrentDate ) ) - Day ( PD_Engagement Date)  + If ( Day ( Get ( CurrentDate ) )  ≥ Day ( PD_Engagement Date); 0 ; If ( Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) < Day ( PD_Engagement Date) ; Day ( PD_Engagement Date) ; Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) ) ) ) & " Days "

This all works well.

What I would like to do is build a case statement which would do the same as now, however, if the termination date had been populated ( in other words not blank)  this would return their length of service at termination date.

So:  Blank termination  field would return currentdate -engagement date ( As the calculation above)

or

Populated pd_TerminationDate field they length of service between pd_TerminationDate and pd_EngagementDate

I have had a fiddle with the above, but I am really struggling!

If any of you could assist, that would be marvellous.

All my best wishes    EssexBiker from a wet and cold Blighty

( Who cancelled Spring BTW?)

• ###### 1. Re: Date Calculation (with a case statement)

UK EssexBiker:

Use the Let function to determine if you want to use the PD_Termination Date field or the current date.  For example, your calculation can be revised to show the following:

Let (CorrectDate = If (IsEmpty(PD_Termination Date); Get (CurrentDate); PD_Termination Date);
GetAsText ( Year ( CorrectDate )  - Year ( PD_Engagement Date) - If ( CorrectDate <  Date ( Month ( PD_Engagement Date) ; Day ( PD_Engagement Date) ; Year ( CorrectDate ) ) ; 1 ; 0 ) ) & " Years, " &
GetAsText ( Mod ( Month ( CorrectDate ) - Month ( PD_Engagement Date) + 12 - If ( Day ( CorrectDate ) < Day ( PD_Engagement Date) ; 1 ; 0 ) ; 12 ) ) & " Months, " &
GetAsText ( Day ( CorrectDate ) - Day ( PD_Engagement Date)  + If ( Day ( CorrectDate )  ≥ Day ( PD_Engagement Date); 0 ; If ( Day ( CorrectDate - Day ( CorrectDate ) ) < Day ( PD_Engagement Date) ; Day ( PD_Engagement Date) ; Day ( CorrectDate - Day ( CorrectDate ) ) ) ) ) & " Days ")

-----

More substitutions can be made, but I wanted to retain as much of your original calculation as possible for readability and understanding.

TSGal
FileMaker, Inc.