Nested IF Statements for same date

Devin Porter

Hello,

I'm trying to create a Nested IF formula utilizing the status bars and due dates. What we're trying to say in English is:

If more than 5 days til due date = Green 

If within 5 days of due date or equal to due date,  = Yellow

If  past due date = Red

I can get certain parts of the formula to work by themselves, but whenever I try to combine them into one cell I get back Unparseable. 

 

The formula I have that works is: 

=IF([Due Date]13 < TODAY(5), "Yellow", "")

or

=IF([Due Date]13 > TODAY(6), "Green", "Red")

When I combine them it breaks and I THINK it's because they contradict each other. As the 2nd one says if it's not greater than 6 days out it's Red, but the first one says if it's less than 5 it's Yellow. I can't figure out how to create this formula to do 3 different statuses. 

 

Help?

 

Thank you!

 

 

TJ (Tracie) Peters

Hi Devin,

You need to nest the IF statements and since you are using statements that may conflict, you have to order the statements appropriately. The logic is linear - the first argument is read and applied first, etc. 

I used this logic in this order:

1. If the date is greater than today, it would change the status to Red.
2. If the date is anything equal to or less than 5 days from today, it's Yellow.
3. Anything within that 5 days to today's date is Green.  

=IF([Due Date]13>TODAY(),"Red",IF([Due Date]13>=TODAY(-5),"Yellow",IF([Due Date]13<TODAY(-5),"Green")))

I hope that was what you were looking for and if anyone has a quicker solution, please post!

Sincerely, TJ

Paul Newcome

=IF([Due Date]@row >= TODAY(5), "Green", IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row < TODAY(5), "Yellow", "")))

 

I also added in that last little "" at the end so that if the due date is blank, so is the RYG cell in the corresponding row.

 

TJ: You have your dates going backwards. Just FYI. I do it all the time then get frustrated when it's working backwards. After a few choice words I finally realize it was user error and have to contact IT for a new keyboard. Hahaha

Devin Porter

Thank you TJ! That worked great. One other question; Do you know if there is a way to have a date range for one of these. Meaning I want it to be yellow if it's 4 days before the due date and 4 days after the due date. Is this possible?

TJ (Tracie) Peters

Thanks, Paul. I may have had the logic wrong. I tested and got the results in the file below ... you are also correct that the formulas can get convoluted and I've had to resist violence against my keyboard too. 

 

TJ (Tracie) Peters

Hi Devin,

Date ranges may have to be done using even more nesting ... I am going to defer to other users as I haven't had too much with date ranges. 

If you don't get a response on this follow-up question in a day or two, I'd post this as a new question. I'd be curious to know the answer too. 

Regards, 

TJ

Paul Newcome

Simply change it to 

 

=IF([Due Date]@row >= TODAY(5), "Green", IF([Due Date]@row < TODAY(-5), "Red", IF(OR([Due Date]@row < TODAY(5), [Due Date]@row > TODAY(-5)), "Yellow", "")))

 

We adjust your red criteria to pick up where the yellow criteria leaves off after adjusting it to account for your desired date range.