Conditional Formatting dates within specific time intervals

[Jay2120]

Hi, I am working on a live Inspections spreadsheet in that the date wil
change automatically everyday. Numerous dates will be entered into th
spreadsheet to indicate when an inspection is due. For example i
todays date is 20/04/2004 and a cleaning inspection is on 20/05/2004
want the cell to go yellow. Then when there are 7 days left until th
inspection i.e. todays date is 13/05/2004 i want the inspection dat
cell (containing date 20/05/04) to change from yellow to orange an
then when the inspection becomes overdue i want the cell to become red


I'm trying to do an if statement behind a cell in that: IF(month(today
date)<1month(inspection date), true, false) If the value is true the
conditional formatting will change the cell colour otherwise it wil
ignore it.

Any help would be most appreciated

 

[Frank Kabel]

Hi
try the following:
- select your cell with the date (assumption: cell B1)
- goto 'Format - Conditional Format' and enter the formula
=(B1>=TODAY()-7)*(B1<=TODAY())
- choose a format (red)

- add a new condition and enter the formula
=B1<TODAY()
and choose your yellow format

Regards
Frank

 

[David]

Most restrictng format mus be first condition, all are formulas
"Formula is: " C3-B5 <=
"Formula is: " C3-B5 <=
"Formula is: " C3-B5 <=30