RAG Status Formula

[Bidds10]

Hi All,

This formula is driving me insane. Could anyone help me with the below please? I'm trying to insert a new RAG/SLA status so I don't have to manually change.

RAG =
Before today = "Red"
Is today = "Amber"
Tomorrow = "Yellow"
In future = "White"

The dates are determined by 3x date columns. Attend Target, Attend Actual and Fix Target.
If 'Fix Target' is before today then "Red", If blank then check if there is a date in 'Attend Target' or 'Attend Actual' If all blank then "White". Else use the closest date.

I hope this makes sense, I've added a picture example of the statuses and dates, Any help would be greatly appreciated.

TIA.


P.S I managed to get this far, but the only status which seems to be correct is "Red" -

=IF(OR(AU5017<TODAY(), AS5017<TODAY()), "Red", IF(OR(ISBLANK(AU5017), ISBLANK(AS5017)), IF(AU5017<TODAY(), "Red", IF(AS5017<TODAY(), "Red")), IF(OR(AU5017=TODAY(), AS5017=TODAY()), "Amber", IF(OR(AU5017=TODAY()+1, AS5017=TODAY()+1), "Yellow", "White"))))

 

[RoryA]

So the closest date part only applies to the actual and target attend columns?

 

[Bidds10]

So the closest date part only applies to the actual and target attend columns?

Yes that's right. If there are dates in all the columns. It will go from the Fix Target date.

 

[dreid1011]

Hi ya, Thank you for the reply! But no sorry. This is still coming out wrong. Please see below.

Your column in the one on the right. The correct one is the one on the left.

View attachment 103324

Then I don't understand your rules. Because I entered the results both manually and with a formula, and the formula matched what I thought I understood your rules to mean. I will take another look and see if I can adjust the formula. I think the biggest misunderstanding is that you said you in your original post to look at Fix Target Date first, so that is what I was doing and ignoring the other two dates if Fix Target Date was not blank.

Is Fix Target Date only useful if it is before today? And if it is not before today, then look at the other two dates?

 

[dreid1011]

Why is row 95 Amber and not Red?

All dates (excluding FIX TARGET DATE) are before today. Even if I change Fix to a later day considering today is now the 11th, it shows red.

Attend Target	Attend Actual	Fix Target
20/10/2023	9/10/2023	11/12/2023

 

[Bidds10]

Why is row 95 Amber and not Red?

All dates (excluding FIX TARGET DATE) are before today. Even if I change Fix to a later day considering today is now the 11th, it shows red.

Attend Target	Attend Actual	Fix Target
20/10/2023	9/10/2023	11/12/2023

Hi ya, row 95 is amber because there is an attend target date, there is an attend actual date and the fix target date was today (yesterday) so sla status is amber.

 

[dreid1011]

Hi ya, row 95 is amber because there is an attend target date, there is an attend actual date and the fix target date was today (yesterday) so sla status is amber.

Okay, I need some serious clarification of your conditions then. This is how I was understanding them based on your original post:

"If 'Fix Target' is before today then "Red", If blank then check if there is a date in 'Attend Target' or 'Attend Actual' If all blank then "White". Else use the closest date."

First, I thought I was looking at FIX TARGET DATE first before anything else, and ignoring all other dates if FIX TARGET DATE was not blank. - That was incorrect.
Then, I thought I was only looking at FIX TARGET DATE if it was before today, then using ATTEND TARGET/ACTUAL DATE (whichever was closest to today). - That was incorrect.

Please explain in full detail the priority of the dates. I'm confusing myself more as I try to wrap my head around what I thought your conditions meant and your follow-up posts.