AnsweredAssumed Answered

Date Calculation (with a case statement)

Question asked by EssexBiker on Mar 26, 2015
Latest reply on Apr 14, 2015 by TSGal

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?)

 

Outcomes