conditional formatting

[xljgd]

This is a very simple CF but i cannot get the logic.


I have


Eg

Target date - 2011/3/21

Expected Date - 2011/4/31


cF rules i have applied


if Expected Date < Target Date then fill green in the expected date

if Expected Date > Target Date + 30 than fill red. that is if the expected date exceeds 30 days beyond the target date say red.


i also have a formula which tells me how many days i am off target.

the problem is i have to set amber when the expected is greater than 1 to 29 days period. i.e once it reaches 30 days it will turn red. but how to capture the days in between.


my red and green cf works right now.


thanks again for your time and inputs.

 

[Hui]

Xljgd


Can you post the Cell Addresses of the various dates?

or better still post the file?

 

[xljgd]

Hello Hui,


I will try to give you the cell addresses.


Target date - G16 23-May-2012

Expected date - G18 31-july-2012


CF used on expected date. I want the expected date to turn

green if it is before the target date - CF formula =$G$18<$G$16 turn green.


amber past the target date but before it reaches 30 days overdue.??


red past due of the target date + 30 giving a 30 day allowance. ==$G$18>$G$16+30 turn red


with the above eg. the expected cell turns red.


but when the date is between 24-May-2012 and 22-June-2012 it should turn amber because it has a 30 day allowance before it becomes overdue.


i hope this helps.

 

[Hui]

Xljgd

Just add a new CF rule

=AND($G$18>=$G$16,$G$18<=($G$16+30))

and set the format to Amber

 

[xljgd]

that did it.i was struggling with getting the syntax right. thanks once again. you are a saviour.