Track Annual Inspection Due Dates Using Conditional Formatting

I am trying to make a worksheet that will track the annual inspection dates
for my equipment and basically change color when the inspections are comming
due or are overdue. Im using conditional foramtting what would the formula be
if i did an inspection on 6-sep-05 and i want excel to tell me when it is 5
days before the due date which would be 6-sep-06.

Select the date cells, let's assume starting at A1
Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =AND(A1+5>=TODAY(),A1<=TODAY())
Click the Format button
Select the Pattern Tab
Select an appropriate highlighting colour
OK
OK


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Scatman" <[email protected]> wrote in message
news:[email protected]...
> I am trying to make a worksheet that will track the annual inspection
dates
> for my equipment and basically change color when the inspections are
comming
> due or are overdue. Im using conditional foramtting what would the formula
be
> if i did an inspection on 6-sep-05 and i want excel to tell me when it is
5
> days before the due date which would be 6-sep-06.

For a date in cell A1, use Format / Conditional Formatting... Formula is... with the formula

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))<(NOW()+5)

and set your formatting colors as you like.



HTH,
Bernie
MS Excel MVP


"Scatman" <[email protected]> wrote in message
news:[email protected]...
>I am trying to make a worksheet that will track the annual inspection dates
> for my equipment and basically change color when the inspections are comming
> due or are overdue. Im using conditional foramtting what would the formula be
> if i did an inspection on 6-sep-05 and i want excel to tell me when it is 5
> days before the due date which would be 6-sep-06.

=A1+365>=TODAY()

assuming a1 is the last inspection date although this might result in the first warning date being a saturday or sunday

Hello all I need help trying to setup a formula, this formula needs to Change the dates and track the following years inspection dates by using three colors "30 days out=yellow,15 days out=orange and 5 days out=red". Each column has the Prior years inspection and then I would like the coming years date to automatically fill once the prior year has been changed. And so on here Is a rough draft of the spread sheet. Please help!!!!

Might want to start your own thread instead of digging up one from over 8 years ago.