# Traffic Light based on % Completed and Due Date

Options
edited 06/22/22

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

• ✭✭✭✭✭✭
Options

Try changing 0.05 to 0,05 with a comma.

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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%-

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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.

• ✭✭✭✭✭✭
Options

Try changing 0.05 to 0,05 with a comma.

• Options

It worked !!! Thanks for all the support

Cheers from Mexico

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!