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", "")
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!
Comments
-
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
-
=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
-
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?
-
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.
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!