Conditional formatting with dates using the RAG status

[cda2212]

Good Day all

I have been experiencing an issues with a very small spreadsheet, causing me a major headache. I have a set of key dates across columns M, N, O on row 4.

M4= First Draft
N4=Final
O4= Mobilization/ Start Date

First draft needs to be received 8 weeks prior to mobilization and final draft needs to be received 4 weeks prior to mobilization.

The dates will be pre-populated using the sum function as we know what the mobilization dates are but I am struggling with the RAG status conditional formatting.

Ideally, I need the RAG status to change automatically to flag green to amber to red as the mobilization date draws closer. I believe it's the IF function I need to make this work??

Any help would be greatly appreciated. Thank you.

 

[Gerald Higgins]

Hi, welcome to the board.

Your post is a little vague, but your request is probably do-able, using Conditional Formating.
The main thing which you need to be clear about is WHEN exactly do you want things to turn from green to amber, and then to red ?
For example you might want it to be amber the week before mobilisation, and red if the mobilisation date has passed.
OR, you might want it to be amber two weeks before mob'n, and red one week before.
And so on.

 

[cda2212]

Oh gosh.. ok
So for the Final it should go red 1 week before mobilization, amber weeks 2 and 3 and green week 4
For the 1st draft, red 5-8 weeks leading up to mobilization, amber weeks 3 and 4, green weeks 1 and 2

BUT , all of final need to go red if draft is received withing weeks 4 to 8

Hope that makes sense

 

[oldbrewer]

project	first draft PL	first draft ACT	final PL	final ACT	mob/start PL	mob start ACT
1	01/08/2018	24/07/2018	01/10/2018	27/09/2018	01/11/2018	28/10/2018
2	02/08/2018	04/08/2018	02/10/2018	05/10/2018	02/11/2018
3	03/08/2018		01/01/2019		01/04/2019
								today's date		11/12/2018
	all dates in act columns are green in row 2
	in row 3 4/8/18 is red 5/10/18 is red and the blank cell in last column is purple
	in row 4 the third column is purple the other cells are not coloured			on time or early		GREEN
								late		RED
								not started but beyond planned start		PURPLE
	3 conditiona format formulas used
	(as in cell C2 which is 24/7/18)
	=AND(C2="",$P$1-B2>0)	for purple
	=AND(C2<>"",C2>B2)	for red
	=AND(C2<>"",C2<=B2)	for green

<colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[cda2212]

Key Dates
1st Draft	Final	Mob/St. Date	Demob/Ex. Date
		12/12/2018

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>

Sorry wasn't very clear again!
So for the Final it should go red if document receipt falls 1 week before mobilization, amber weeks 2 and 3 and green week 4
For the 1st draft should turn red if document receipt date falls 5-8 weeks leading up to mobilization, amber weeks 3 and 4, green weeks 1 and 2

BUT , all of final need to go red if draft is received withing weeks 4 to 8

Hope that makes better sense

 

[oldbrewer]

my reply was generic to give you a steer for the formulas - can you not now apply them to your situation ?