Formula for Lights

Brennan Montoni

Hi all,

I have a sheet that contains fields for "Preparer Due Date" and "Preparer Status" and am trying to add in the following logic to a formula:

Blue = completed

Green = task not started or in process that are due more than 3 days away

Yellow = task not started or in process that are due within the next 3 days

Red = task not started or in process that are past due

Thanks in advance!

Paul Newcome

Try something like this...

=IF([Preparer Status]@row = "Completed", "Blue", IF([Preparer Due Date]@row < TODAY(), "Red", IF([Preparer Due Date]@row <= TODAY(3), "Yellow", "Green")))

Ariana Arden

Hi Brennan!

I have many similar formulas, they're great. Try the below. 🙂

=IF([Preparer Status]@row = "Completed", "Blue", IF(AND(OR([Preparer Status]@row = "In Process", [Preparer Status]@row = "Not Started"), [Preparer Due Date]@row > TODAY(3)), "Green", IF(AND(OR([Preparer Status]@row = "In Process", [Preparer Status]@row = "Not Started"), [Preparer Due Date]@row <= TODAY(3), [Preparer Due Date]@row > TODAY()), "Yellow", IF(AND(OR([Preparer Status]@row = "In Process", [Preparer Status]@row = "Not Started"), [Preparer Due Date]@row < TODAY()), "Red", "Error"))))

It's a little long but it should work for you. I also added in a condition so that if the Preparer Status column is blank, the formula will display "Error" to let you know.

Paul Newcome

@Ariana Arden Since you have already established that the [Preparer Status] must be either "In Process" or "Not Started" by getting past the first IF statement, you shouldn't have to repeat that criteria set with the AND/OR combo for the rest of the IFs. You should be able to just reference the date. If you wanted to account for a blank [Preparer Status, you could include an IF for it as the second (or first) argument so that if both of the first two are false (Complete and blank) then it must be one of the other two and still leave those as implied.

=IF([Preparer Status]@row = "Completed", "Blue", IF([Preparer Status]@row = "", "Error", IF([Preparer Due Date]@row < TODAY(), "Red", IF([Preparer Due Date]@row <= TODAY(3), "Yellow", "Green"))))

Or even...

=IF([Preparer Status]@row <> "", IF([Preparer Status]@row = "Completed", "Blue", IF([Preparer Due Date]@row < TODAY(), "Red", IF([Preparer Due Date]@row <= TODAY(3), "Yellow", "Green"))), "Error")

Leveraging the implied with nested IFs can really save you a lot of typing.