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

    Days until Expiry

    hrcap

      Title

      Days until Expiry

      Post

           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:

            

            

           Let(
            
           @Expiry_Days = TextFormatRemove ( ( expiry_date - (Get(CurrentDate) ))) ;
            
            
            
           Case(
            
           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)) ;
            
           @Expiry_days
            
           )
            
           )
            
            
            
           many thanks
            
           Hadleigh

        • 1. Re: Days until Expiry
          philmodjunk

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