Conditional Formatting dates with text

[summer2010]

Conditional format text with date.

Please can someone help me? I am new to excel and have been asked to set up a supervision schedule for some of my colleagues.

I have been trying to do the following:

IF date in B2 is over due by more than 1 week to show RED if C2 is blank.

If Date in B2 is due within 2 weeks To show Amber if C2 is blank

Date in B2 to show Green If C2 is Yes. C2 to also show green if yes, otherwise white if blank


B2 to show white if more than 3 weeks.

I've attached the file to show what I'm hoping to do, this would continue along the columns

I'd be grateful for your help

[edvwvw]

Welcome to the board

I have presumed that you are checking against the current date

Condition 1 =AND((NOW()+7>B2),ISBLANK(C2))
Condition 2 =AND((NOW()+14=>B2,(NOW()-14=<B2),ISBLANK(C2))
Condition 3 =AND(C2="YES")

edvwvw

[NBVC]

Try these 3 conditions:

=AND($B2<>"",$B2<=TODAY()-7,$C2="")

=AND($B2>=TODAY(),$B2<=TODAY()+14,$C2="")

=$C2="Yes"

[summer2010]

Hello
Many Thanks your help on this, Edvwvw - I'll be using date's that will be put into B2, B3 etc. Condition 1works perfectly for what I'm looking for, but when I input condition 2, and error sign show's up, showing that the = is an error. May I ask if you can help on that one please? I've tried inputting differnt solutions but am not coming up with anything.

For condition 2, I would like it to show amber if the date in B2 is i.e 08/10/10 (from now it's 14 days) but I would like the amber to show up from the day it's due till the 14th day, then after the 14th day to go back to a white background.

My condition 3 would go green in B2 when C2 says yes as you've put in your condition 3, but I can't work that one out until I have put in condition 2.

I apologise if I'm not making sense, as excel is very new to me.

[summer2010]

I've cracked it with a bit more playing about with the formulas!!!! thank you so much for your help. I am so grateful.

[summer2010]

Could I ask for your help again please?? with the CF in place when the cells are blank in B2 the cell now turns red, I can get it to turn white but have the font as red when the date is over due, but ideally I would like the background to be white when the cell is blank and the background to show as red and the font black when overdue. Can you advise on how I can change this please?

Many Thanks