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]

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

 

[cda2212]

[TABLE="width: 376"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD="colspan: 4"]Key Dates[/TD]
[/TR]
[TR]
[TD]1st Draft[/TD]
[TD]Final[/TD]
[TD]Mob/St. Date[/TD]
[TD]Demob/Ex. Date[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]12/12/2018[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

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 ?