4 Replies Latest reply on Oct 19, 2014 3:06 AM by raybaudi

    Due Date

    thong127

      Title

      Due Date

      Post

      Hello,

      I have two date fields Expiry Date and Retest Date.

      I want to calculate how many number of days remaining  before the expiry or retest date in one field which I called Due Date field and if the result is 0, it will appear as expired.

      Can you please help me how to achieve this.

      Thank you so much.

        • 1. Re: Due Date
          dejosejimpaul

          Make your Due Date field a calculation field and specify a calculation like below:

          If ( ExpiryDate - Get ( CurrentDate ) ≤ 0 ; "Expired" ; ExpiryDate - Get ( CurrentDate ) )

          Calculation result must be Text.

          Note that the calculation above only uses the ExpiryDate field. If you want to include also the Retest field, you can make a calculation that uses the Case function to specify results with various conditions.

          • 2. Re: Due Date
            raybaudi

            Try this in an UNSTORED calculation field, result text:

            Let(
            d = Get ( CurrentDate ) - Min ( Expiry Date ; Retest Date ) ;
            Case(
            d ≥ 0 ; "expired" ;
            Abs ( d )
            )
            )

            • 3. Re: Due Date
              thong127

              Hi Raybaudi,

              Thank you so much, please kindly extend your help, how can I put "Day to go" or Days to go" after the number.

              Thank you.

              • 4. Re: Due Date
                raybaudi

                Let([
                d = Get ( CurrentDate ) - Min ( Expiry Date ; Retest Date ) ;
                p = If ( Abs ( d ) > 1 ; "s" )
                ];
                Case(
                d ≥ 0 ; "expired" ;
                Abs ( d ) & " Day" & p & " to go"
                )
                )