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

    Date Calculation (with a case statement)

    EssexBiker

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

          UK EssexBiker:

          Thank you for your post.

          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.