Conditional Formatting (IF Statement)

[Sandy75]

Hi All,
I've got a spreadsheet where the user enters a date (Say in Cell A1) and
another date 50 days ahead is automatically generated in another cell (Say C1). I'd like a third cell (Say E1) to turn Amber if todays date is within 14 Days of C1, Red if todays date goes past C1 and otherwise Green if neither of these conditions is met.

Hope i've explained it ok,
Thanks all

 

[VoG]

Try Conditional Formatting http://www.contextures.com/xlCondFormat02.html

The formula for your first condition would be

=C1-14<=TODAY()

 

[Sandy75]

Thanks for the reply Peter. I tried that formula in the conditional formatting box but it turns the cell amber once it's less than todays date. I need it to be less than todays date but within 14 days

Sandy

 

[VoG]

I think that my formula was the wrong way round. Maybe

=TODAY()<=C1-14

 

[Sandy75]

Okay i slightly changed your formula to

=TODAY()>=C1-14 and it works now but i'm having trouble getting the other conditions to work. If todays date exceeds the date in C1 i want the cell to turn red but it's not working. The formula i'm using is

=TODAY()>C1

Is my formula wrong ?

Thanks,
Sandy

 

[VoG]

The formula is correct but the order that you enter the CFs is important.

Try making =TODAY()>C1 your first condition and =TODAY()>=C1-14 the second.

 

[Sandy75]

That's it working now Peter. Thank you for taking the time to help me...i appreciate it.

Sandy xo