Stoplight based on date and status

Megan TW

Hi Community!

I'm at a hard stop on stoplights (pun intended).

I am looking to populate stoplights on a sheet based on days to due date and status of a line. Columns on sheet below, with the "At risk?" being where I'd like my stoplights:

I would like to populate a green stoplight for the following conditions:

-If today's date is 8 or more days from the due date and the status is "Schedule"

-If today's date is 7 or more days from the due date and the status is "Production"

-If today's date is 6 or more days from the due date and the status is "Ext. Lab"

-If today's date is 5 or more days from the due date and the status is "Packaging"

-If today's date is 4 or more days from the due date and the status is "Shipping"

I would like to populate a red stoplight for the following conditions:

-If today's date is 5 days or less from the due date and the status is "Schedule"

-If today's date is 4 days or less from the due date and the status is "Production"

-If today's date is 3 days or less from the due date and the status is "Ext. Lab"

-If today's date is 2 days or less from the due date and the status is "Packaging"

-If today's date is 1 day or less from the due date and the status is "Shipping"

Is this an impossible task? Halp!

Megan TW

Okay. I think I've almost got it, but I'm getting something weird. Further down the rows using this formula: =IF(Status@row = "Schedule", IF([Days until Due]@row >= 10, "Green", IF([Days until Due]@row < 9, "Red", "Yellow"))) + IF(Status@row = "Production", IF([Days until Due]@row >= 10, "Green", IF([Days until Due]@row < 8, "Red", "Yellow"))) + IF(Status@row = "ExtLabMPPT", IF([Days until Due]@row >= 6, "Green", IF([Days until Due]@row < 3, "Red", "Yellow"))) + IF(Status@row = "PackagingFG01", IF([Days until Due]@row >= 6, "Green", IF([Days until Due]@row < 3, "Red", "Yellow"))) + IF(Status@row = "Shipping", IF([Days until Due]@row >= 4, "Green", IF([Days until Due]@row < 2, "Red", "Yellow")))