Highlighting date cells before the event elapses

[Guest]

I need to set up an equipment calibration chart. This means that I will need
to look down the chart and see if a piece of equipment is due for testing
(about 2 months should do it). If anyone has any ideas about how to flag up
the cell with the due date I would be hugely appreciative.

I'm using office 2000 (I think)

 

[JulieD]

Hi

lots of ways to approach this .... depending on how much information you
want stored (ie do you want a history of equipment calibration or are you
just going to keep last & next dates for each item?).

if you want just last & next then one way would be
.............A..................B................C...............................................D
1..........Item.........Last Date.........Mths to Next
Calibration........Next Date
2........Item
1........1/1/05..............6.............................................=DATE(YEAR(A2),MONTH(A2)+C2,DAY(A2))

this will then calculate the date of the next calibration for you for each
item.

Then select column D choose format / conditional formatting
choose formula is
type
=$D2<=TODAY()
click format ... go to patterns and choose red click OK
-this will make the cell go red if the date of the next calibration has
passed

then still in format / conditional formatting click ADD
choose
formula is
type
=$D2<=DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY()))
click format .. go to patterns and choose a lovely lime green click OK twice

this will make the cell go lime green if the calibration is due any time in
the next two months

hope this helps