Yellow if, Red if

ZDevier

Hello - looking for some assistance on a formula that I'm trying to create that will return my task health as Yellow if 1 - 13 days beyond target completion date, and Red if 14 days beyond. I have created the below, which works, however I'm stumped on where I need to incorporate the IF formula for Yellow, and how to ensure they are both active:

=IF(ISBLANK(Status@row), "", IF(AND([Target/Actual Completion Date]@row < TODAY(+14), Status@row <> "Complete"), "Red", IF(Status@row = "Not Started", "-", IF(Status@row = "In Process", "Yellow", IF(Status@row = "Complete", "Green")))))

I've tried the below, and it works, but it will only report health as Red regardless of how many days over the task is:

=IF(ISBLANK(Status@row), "", IF(AND([Target/Actual Completion Date]@row < TODAY(+14), Status@row <> "Complete"), "Red", IF(Status@row = "Not Started", "-", IF(Status@row = "In Process", "Yellow", IF(Status@row = "Complete", "Green", IF(AND([Target/Actual Completion Date]@row < TODAY(+1), Status@row <> "Complete", "Yellow")))))

ro.fei

Hey @ZDevier ! It looks like it's only returning red because all you're checking for is if the Target/Actual Completion Date is less than 14 Days after the current date. I believe you may want to switch your less than sign to a greater than sign. If that doesn't work, I would try using the NETDAYS function to set your parameters.

Hope this helps! Let me know if you're still having issues & I'll be happy to help out some more. 😊