# Nested IF Statements for same date

Options
edited 12/09/19

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!

Tags:

• Options

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

• ✭✭✭✭✭✭
edited 07/09/18
Options

=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

• Options

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?

• Options

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.

• Options

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

• ✭✭✭✭✭✭
Options

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!