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:

If Preparer Status is "Complete" OR Preparer Due Date is earlier than today AND Preparer Status is NOT "Complete", then put a green circle

If Preparer Status has a status of Not Started or and is due within 3 days, then put a yellow circle

If Preparer Status has a status of Not Started or In Progress AND Preparer Due Date is in the past, then put a red circle

Thanks in advance!

Josh G

I think I got it.

=IF(AND(OR([Preparer Status]@row = "Not Started", [Preparer Status]@row = "In Progress"), [Preparer Due Date]@row < TODAY()), "Red", IF(AND([Preparer Status]@row = "Not Started", [Preparer Due Date]@row >= TODAY(), [Preparer Due Date]@row <= TODAY() + 3), "Yellow", IF(OR([Preparer Status]@row = "Complete", AND([Preparer Due Date]@row <> "", [Preparer Due Date]@row > TODAY(), [Preparer Status]@row <> "Complete")), "Green", "")))

Josh G

I think I got it.

=IF(AND(OR([Preparer Status]@row = "Not Started", [Preparer Status]@row = "In Progress"), [Preparer Due Date]@row < TODAY()), "Red", IF(AND([Preparer Status]@row = "Not Started", [Preparer Due Date]@row >= TODAY(), [Preparer Due Date]@row <= TODAY() + 3), "Yellow", IF(OR([Preparer Status]@row = "Complete", AND([Preparer Due Date]@row <> "", [Preparer Due Date]@row > TODAY(), [Preparer Status]@row <> "Complete")), "Green", "")))

Brennan Montoni

Thanks, Josh.

Would it be possible to add in if the task was due in the past and is Complete, the put green? Also, could we add if the task is due in the future and In Process, then put green?

As of now these these instances are showing as blank (no circle appears at all).

Brennan Montoni

Actually, if the status is complete, could we just return a blue circle?

So...

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 in the next 3 days

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

Josh G

Here's a simpler formula that gives you the most recent set of rules. It'll yield a blank if there is an exception.

=IF(OR([Preparer Status]@row = "Not Started", [Preparer Status]@row = "In Progress"), IF([Preparer Due Date]@row < TODAY(), "Red", IF([Preparer Due Date]@row < TODAY() + 3, "Yellow", "Green")), IF([Preparer Status]@row = "Complete", "Blue", ""))

Brennan Montoni

Thank you Josh.

Can we add one more statement in?

I'd like to have a gray circle for any task that is Not Started and not sure within 3 days.

Thanks in advance!