Calculation Help - Project Traffic Light RGY Based on Due Date, % Completed and Status

cherie.ryan.KDP

Hello I need a calculation assist.

Columns: Start, Due, Duration, % Completed, Status

Status drop downs:

Not Started,

In Progress,

Overdue,

Completed.

Need traffic light based on the following:

Complete 100% = Blue

Not Started 0% Complete

Due Date Over 7 Days = Green

Due Date Less than 5 Days = Yellow

Due Date Less than 2 Days = Red

In Progress 1%-99%

Due Date over 7 Days = Green

Due Date Less than 5 Days = Yellow

Due Date Less than 2 Days = Red

Overdue Any % = Red

cherie.ryan.KDP

Help please :)

KPH

Hi @cherie.ryan.KDP

Assuming the Due column is a date. In your symbol column you will need an IF function. I suggest you build this one part at a time and check it works, that will make it a lot easier to identify any missing commas or parenthesis, and will help to validate the logic.

Start with the blue:

=IF([% Complete]@row = 1, "Blue")

or

=IF(Status@row = "Completed", "Blue")

Either would work to turn the symbol blue, but I am going to use the Status column.

Then you can add another IF that is only evaluated if the first is false. Add the part in bold for red.

=IF(Status@row = "Completed", "Blue", IF(Due@row <= TODAY(2), "Red"))

This will turn the symbol red if the status is not completed and the due date is less than or equal to two days from today.

Then another IF to be evaluated if both of those are false:

=IF(Status@row = "Completed", "Blue", IF(Due@row <= TODAY(2), "Red", IF(Due@row <= TODAY(5), "Yellow")))

This will turn the symbol yellow if the status is not completed and the due date is not less than or equal to two days from today but is less than or equal to 5 days from today.

And you can probably guess what comes next. Another IF to be evaluated if the first 3 are false:

=IF(Status@row = "Completed", "Blue", IF(Due@row <= TODAY(2), "Red", IF(Due@row <= TODAY(5), "Yellow", IF(Due@row > TODAY(5), "Green"))))

This will turn the symbol green if the status is not completed and the due date is not less than or equal to two days from today and is also not less than or equal to 5 days from today, but is more than 5 days from today.

cherie.ryan.KDP

THANK YOU!!!!!

KPH

Glad I could help.