RAG Status, please help

[Kimlou]

Hi,
So, I'm not great on excel. But i need to create a rag status based on start date (column E6), estimated completion daye (column G6) and actual completion dates (column H6). I want the rag status column (I6) to be blank when there is nothing filled out, green when the actual completion date is within the estimate, amber when the actual completion date is soon becoming due and red when the date has been exceeded. I have been trying to do this now for 2 days, to no avail using IF and conditional formatting. Can anyone please help me? Thank you

 

[MARK858]

green when the actual completion date is within the estimate, amber when the actual completion date is soon becoming due and red when the date has been exceeded.

I think you will need to provide some guideline numbers for the criteria before anyone can provide any solution.

 

[Kimlou]

I think you will need to provide some guideline numbers for the criteria before anyone can provide any solution.

Hi,
It can literally be any dates at all. I did try to add a picture, but it won't let me for some reason.
So let's say the start date is 01/02/2020, estimated completion date is 15/02/2020 and the actual completion date is 16/02/2020 or after, I would want the rag status to show as red.

If the actual completion date was 14/02/2020 or before, I would want it to show as green.

If the actual completion date wasn't yet filled out, but it is coming due soon, then I would want it to show as amber.

But I want it to show like this for any dates that I put in.

Hopefully this explains it a bit better. Thank you

 

[MARK858]

coming due soon

The explanation in the original post was fine but we can't really provide an answer unless you define phases like the above. Is "soon" a day, 3 days, a week, a year? See what I mean? and it is the same with "date is within the estimate".

 

[Kimlou]

 

[Kimlou]

The explanation in the original post was fine but we can't really provide an answer unless you define phases like the above. Is "soon" a day, 3 days, a week, a year? See what I mean? and it is the same with "date is within the estimate".

Ah sorry, I get you now.

So I would like the rag status to be amber when it is within a week of the estimated completion date and the actual completion date hasn't been filled out. And then red when the actual completion date is filled out and surpasses the estimated completion date

 

[MARK858]

Maybe...

Book1
	E	F	G	H	I
3	Start date	Weeks	Estimate	Actual	RAG
4	16/01/2020	5	20/02/2020	19/02/2020
Sheet1

Cell Formulas
Range	Formula
G4	G4	=E4+(F4*7)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I4	Expression	=OR(AND(TODAY()>G4,H4=""),H4>G4)	text	YES
I4	Expression	=AND(H4="",TODAY()>G4-7,TODAY()<=G4)	text	YES
I4	Expression	=OR(AND(OR(H4="",H4<G4-7),TODAY()<G4),H4<=G4)	text	YES

 

[Kimlou]

Thank you, this looks great. I'll give it a go on my spreadsheet and hopefully it works for what I need. Thank you so much for your help

 

[Kimlou]

Maybe...

Book1
	E	F	G	H	I
3	Start date	Weeks	Estimate	Actual	RAG
4	16/01/2020	5	20/02/2020	19/02/2020
Sheet1

Cell Formulas
Range	Formula
G4	G4	=E4+(F4*7)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I4	Expression	=OR(AND(TODAY()>G4,H4=""),H4>G4)	text	YES
I4	Expression	=AND(H4="",TODAY()>G4-7,TODAY()<=G4)	text	YES
I4	Expression	=OR(AND(OR(H4="",H4<G4-7),TODAY()<G4),H4<=G4)	text	YES

Hi Mark858, so I've tried this and I've got the green working, but the red is on all the time, unless changing to green and the amber wont work at all. I don't know if I am doing the wrong selections when going on to conditional formatting, as I'm not sure what expression is.

 

[MARK858]

Works for me, see the attached. First of all format E4,G4 and H4 as dates as I have left them as numbers in case you are on a different date format to me.
E4 should show 18th Jan 2020 and I4 amber.
Change E4 to 16th Jan 2020 and it should be red.

Kiml.xlsx | Powered by Box

app.box.com