1 Reply Latest reply on Sep 6, 2013 6:31 AM by philmodjunk

    Days until Expiry



      Days until Expiry


           Afternoon All


           I have made a field to work out the days until the expiry of a certificate, the calculation also turns the number of days to an amber colour if there are less than 28 days but more than 0 days to expiry.

           At the moment this calculation works fine unless there are between 3-9 days to expiry in which case it does not change the colour of the text.

           Any input would be appreciated.


           please see the calculation below:



           @Expiry_Days = TextFormatRemove ( ( expiry_date - (Get(CurrentDate) ))) ;
           expiry_date_portal = ""  ; "" ;
           expiry_date_portal = "N/A"  ; TextFormatRemove( "N/A") ;
           @Expiry_days  ≤ 28 and
           @Expiry_days  > 0 ;
           TextColor(@Expiry_days; RGB(255;127;0)) ;
           @Expiry_days  ≤ 0 ;
           TextColor(@Expiry_days; RGB(255;0;0)) ;
           many thanks

        • 1. Re: Days until Expiry

               A case function returns the result for the first boolean expression inside it to evaluate to True.

               Why don't you use a conditional format expression? That would separate this into two calculations--one for applying the color change and one for computing the number of expiry days.

               I think your textFormatRemove function is producing a text result. "9" is greater than "28" when the values are text rather than number. Change the first term to:

               @Expiry_Days = getasnumber ( TextFormatRemove ( ( expiry_date - (Get(CurrentDate) ))) ) ;