Status indicators

edited 12/09/19 in Smartsheet Basics


 I am trying to create status indicators based on Schedule Variance range. eg if the schedule variance range is between -1 to -5 then status is yellow, If greater than -1 it is Green , If less than -5 it is red..

Any help would be great;y appreciated


  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    Hi priyankaprim2,

    Assuming you already have a Schedule Variance value in a column, I think this may be what you're after:

    =IF(AND([Schedule Variance]1 <= -1, [Schedule Variance]1 >=-5), "Yellow", IF([Schedule Variance]1 < -5, "Red", "Green"))

    If you're not already calculating variances:

    =IF(AND([Finish Date]1 <= (TODAY() - 1), [Finish Date]1 >= (TODAY() - 5)), "Yellow", IF([Finish Date]1 < TODAY() - 5, "Red", "Green"))

    And assuming you only want to take weekdays into consideration:

    =IF(AND([Finish Date]1 <= WORKDAY(TODAY(), -1), [Finish Date]1 >= WORKDAY(TODAY(), -5)), "Yellow", IF([Finish Date]1 < WORKDAY(TODAY(), -5), "Red", "Green"))

    Hope this helps.

    Kind regards,

    Chris McKay