RAG = Date Range formula?!
Hi,
I'm setting up a sheet to keep a track of training and re-training needs.
I need the RAG status to change according to the date range, e.g.:
Person A | Training Due Within 90 Days of Today | RAG = Yellow
Person B | Training Due Anytime Over 90 days from Today | Rag = Green
Person C | Training Overdue from today up to any amount of days in the past | RAG = Red
I've attempted creating a formula on 0 coding ability, and it works in as much as turning every row yellow... So, can anybody help create a formula for the above need, and/or translate the formula I've botched together and why it wouldn't work for the red or green?
=IF([Refresher Due Date]6 >= TODAY() + 30, "Yellow", IF([Refresher Due Date]6 > TODAY() + 90, "Green", IF([Refresher Due Date]6 <= TODAY(), "Red")))
Answers
-
Nested IF statements work from left to right and stop on the first true value. If something is 150 days out, that is greater than 30 days out, so it flags it as yellow and stops.
Try reordering the IF statements so that greater than 90 is checked before greater than 30.
-
@Paul Newcome unfortunately that just seems to have left the column blank - not invalid, just blank?
-
What is your newest formula?
If you can clarify what you want, I'd be happy to help write it out. Your text portion of your original post indicates you want
Red for overdue, yellow for within the next 90 days, and green for 90+ days
but the formula in the original post is basically saying
Red for overdue, yellow for 30+ days and green for 90+ days
-
Okay, so the most recent one is:
=IF([Refresher Due Date]6 > TODAY() + 90, "Green", IF([Refresher Due Date]6 <= TODAY() + 90, "Yellow", IF([Refresher Due Date]6 <= TODAY(), "Red")))
Using this formula has now taken me back to yellow for everything (including the ones that definitely should be red!)
Your understanding is spot on - red for overdue, yellow for within 90 and green for 90+. :-) Any help appreciated at this stage!!
-
Ok. Getting closer, but still running into the same logic issue of stopping on the first true.
Think of dates as numbers. Today = 0, future is positive numbers and past is negative numbers.
Your "Yellow" argument is saying anything less than (+90). Technically negative numbers are less than positive 90, so even though it is in the past, it is still technically true for the "Yellow" argument as well.
Another little trick when using nested IFs like this is basically the exact opposite thinking. If it makes it to the second IF then the first MUST be false. Otherwise it would have stopped on the first.
When you have a total of three options, if 1 and 2 are false then all that is left is the 3rd option which means we don't even need to specify criteria for it.
So keeping those two things in mind, take a look at this...
=IF([Refresher Due Date]6 > TODAY() + 90, "Green", IF([Refresher Due Date]6 <= TODAY(), "Red", "Yellow"))
If it is greater than +90 then green. If it is less than 0 then red. If both are false then the only other possibility is that it is greater than 0 and less than 90 so we just set the "value if false" to yellow since the "true" statement is already implied by the first two being false (if that makes any sense).
-
@Paul Newcome although I now have a far better understanding of why/how the IF formulas work in theory... unfortunately this one is still coming up just yellow!
I'm attaching a screenshot of my columns and data in case you can see any practical reason why this formula wouldn't interpret the sheet the way I would like it to... (the RAG status column has been filled out manually here, not a result of any formulas)
-
Hmm... Double check that the Refresher Due Date column is in fact set as a date type column. If it is, how exactly is that column being populated?
-
It's definitely a date type column, but it was info imported from an excel spreadsheet!
-
Ok. It may be that the data is coming across as text values. Insert a helper column real quick and throw this in there as a column formula...
=IF(NOT(ISDATE([refresher Due Date]@row)), "TEXT")
and see what pops up.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!