RAG = Date Range formula?!

nicole.turner

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

Paul Newcome

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.

nicole.turner

@Paul Newcome unfortunately that just seems to have left the column blank - not invalid, just blank?

Paul Newcome

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

nicole.turner

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

Paul Newcome

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).

nicole.turner

@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)

Paul Newcome

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?

nicole.turner

It's definitely a date type column, but it was info imported from an excel spreadsheet!

Paul Newcome

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.