Help with RAG, IF, AND with Today's Date TOO!

[claudine]

Dear All,

I am new to the forums, though not the site (which rocks!) and I really need some help.

I have;

Start Date in A1

Planned Date in A2 (based on rules on turnaround)

Actual Date in A3 (which will remain blank until a user types in a date)

I want A3 to change RAG colour using TODAY() if a date has not been entered yet

BUT

If a date has been entered in A3 - I want A3 to use the main rules and ignore TODAY() so it doesnt continue to change colour.

Have tried an IF with an AND and ISBLANK but am lost.


Help and thanks in advance!

 

[vasim]

Welcome to the forum (Hui's reminder)


On what condition do you want the RAG colour in cell A3 on start date/planned date, and whats the main rule in A3.

 

[claudine]

Wow - thank you for the uber fast response...


A1 Start Date A2 Planned Date A3 Actual Date

A3 should go Red if A3 is blank and TODAY() is greater than or equal to A1+22 days

A3 should go Amber if A3 is blank and TODAY() is between A1+11days and A1+15days

A3 should go Green if A3 is blank and TODAY() is less A1+14 Days


AND THEN


The same date rules if a user enters a date in A3


The PLANNED DATE COLUMN is only for user reference


Hope that is clearer and thank you in advance

 

[vasim]

Sorry,


AND THEN


The same date rules if a user enters a date in A3


WHATS THIS????


And


Amber if A3 is blank..........

REd if A3 is blank...........

Green if A3 is blank.........


Is this really correct????

 

[claudine]

Let me try another way of explaining;


A1 1 June 2011 Start Date

A2 15 June 2011 Planned Date

A3 cell is green if TODAY's date is less than or equal to A1+14 Days and a user has not entered a date in A3 yet

A3 if the user enters 15 June 2011 in A3 then I need the calculation make A3 go green but stop using today's date, so the colour will not change again. If there is a date in A3, I need the coloour to stop changing.


If I can understand how to set this first relationship - I can figure out the amber and green colours.


The tricky bit is that once a user HAS entered a date in A3, I need the calculation to stop using today's date.....

 

[vasim]

Fine, I have something for you hope this helps....


1) Format A3 cell to Amber background

then conditional formatiing from format

2) Under condition one = formula is =$A$1>=TODAY()+22

3) under condition two = cell value is not equal to =""

4) Under condition three = formula is =$A$1<=TODAY()+14


Let me know if this dosent suits you.........

 

[vasim]

Fine, I have something for you hope this helps....


1) Format A3 cell to Amber background

then conditional formatiing from format

2) Under condition one = formula is =$A$1>=TODAY()+22 then format Red

3) under condition two = cell value is not equal to ="" then format no colour

4) Under condition three = formula is =$A$1<=TODAY()+14 then format Green


Let me know if this dosent suits you.........

 

[claudine]

Thank you for this. That's the first part I need.


BUT....


If A3 is blank and TODAY()is >= A1 + 22 days then A3 should go Red

If A3 is blank and TODAY()is <= A1 + 10 days then A3 should go Green

If A3 is blank and TODAY()is between A1+11 days and A1+15 days then A3 should go Amber


THEN When a user enters a value in A3


If A3>= A1 + 22 days then A3 should go Red

If A3<= A1 + 10 days then A3 should go Green

If A3 is between A1+11 days and A1+15 days then A3 should go Amber


I had thought that doing something with an IF and an AND with a bit of an OR might work but am in a bit of a tangle.


Does this make it a bit clearer. Also - my work laptop is still on Excel 2003 so I don't have the best conditional format options to start off with.


Many many thanks in advance!