Traffic Light based on % Completed and Due Date

Hello, I would like you to help me with the following semaphore formula. My premises are:

1.-Green if the activity has not started yet

2.- Green if the activity has already started but there are still 10 days left and it is more than 50% complete

3.-Yellow if less than 75% of the activity has been completed and there are 10 days left before the due date

4.-Red if there are less than 3 days left and the activity is not yet completed

5.- Red if the date has already expired and is not 100% finished

Thanks in advance

Best Answer


  • Hi Alexander. I think we are both looking for the same thing looking at your discussion. It's the range section I'm having problems with. The formula on my discussion might help you get as far i am am in that the RED and GREEN part works, but defaulting the last argument to AMBER doesn't really work.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What if it was 60% with 10 days left? Technically that would flag true for both yellow and green.

    My suggestion would be to restructure your logic so that if we are 50% of the way through the duration of the task then we should be at least 50% complete. For example... We have a 10 day task. At day 5 we should be at least 50% complete.

    Green = 50%+

    Yellow = 45% --> 50%

    Red = 45%-

  • I like that Paul.

    Can you help me out with the traffic light formula please?

    I have this formula so far in spanish where:

    % Completo is % Completed

    Inicio is start day

    Finalizar is due date

    161 is the row

    and verde is green amrillo yellow and rojo red:

    =IF([% Completo]161 = 1; "Verde"; IF(Inicio161 > TODAY(); "Verde"; IF(AND(Finalizar161 < TODAY(); [% Completo]161 <= 1); "Rojo"; IF(AND(Finalizar161 < TODAY(10); [% Completo]161 <= 0,5); "Amarillo"; IF(AND(Finalizar161 = TODAY(); [% Completo]161 >= 0,75); "Verde"; IF(Finalizar161 < TODAY(10); "Rojo"; IF(AND(Finalizar161 > TODAY(); [% Completo]161 <= 0,25); "Amarillo"; IF([% Completo]161 >= 0,5; "Verde"))))))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    First we need to figure out the expected % complete based on today's date.

    =(TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)

    Then we say that if the actual is greater than or equal to the expected (or the actual is 100%), green. If the actual is less than the expected but still within 5%, yellow, and more than 5% behind is red.

    =IF(OR([% Completo]@row = 1, [% Completo]@row >= (TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)); "Verde"; IF([% Completo]@row >= (TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row) - 0.05; "Amarillo"; "Rojo"))

  • Thanks for the advise Paul.

    I tried to write the formula but it says "#UNPARSEABLE". I wrote the following formula:

    =IF(OR([% Completo]161 = 1; [% Completo]161 >= (TODAY() - Inicio161) / (Finalizar161 - Inicio161)); "Verde"; IF([% Completo]161 >= (TODAY() - Inicio161) / (Finalizar161 - Inicio161) - 0.05; "Amarillo"; "Rojo"))

    I don't know what is wrong.

    Thanks in advance!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try changing 0.05 to 0,05 with a comma.

  • It worked !!! Thanks for all the support

    Cheers from Mexico

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!