Traffic Light based on % Completed and Due Date

Alexander Albeck

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

Paul Newcome

Try changing 0.05 to 0,05 with a comma.

Fialko66

https://community.smartsheet.com/discussion/92456/traffic-light-based-on-completed-and-due-date

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

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

Alexander Albeck

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

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

Alexander Albeck

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

Try changing 0.05 to 0,05 with a comma.

Alexander Albeck

It worked !!! Thanks for all the support

Cheers from Mexico

Paul Newcome

Happy to help. 👍️