Nested IF Statements for same date

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", "")

=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!

Find more posts tagged with

Formulas

Comments

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:

If the date is greater than today, it would change the status to Red.
If the date is anything equal to or less than 5 days from today, it's Yellow.
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.

Smartsheet Sample.jpg

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,

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.

Sign in to join the conversation!

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…