auto colored cells with days

[skylinekiller]

I am making a training worksheet. I have columns named
Date last Trained, how often training is required (ex. every 3 year
etc..) and Training due date. My first question is in detailed let
say that I have a date of last trained on 15 Feb 2003 and the due dat
is 15 Feb 2004. I would like due date cell to highlight 3 differen
colors, one for with in 1 month, 2 for with in 3 months and the las
color is green for training not required (all days prior to the
months. So it will be Yellow for 3 months out, Red for one month ou
and after they get trained, it will turn green and remain green until
months from the next due date.

I also want to know if there is a way the date within the cell of du
date can be automatically formatted to advance to the next requred yea
that training is required with out actually inputing it. For example
I trained someone today, 28 April 2004, I inuputted this in the "dat
last trained cell" I would like the due date to automatically chang
the next due date either one year or 3 years etc..

I appreciate any help and the time and effort that you give me

 

[Frank Kabel]

Hi
to give you some starting point:
1. For highlighting use 'Format - Conditional Format'. e.g.
- select the cell (e.g. cell B1)
- goto this dialog
- enter the formula
=B1>DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())
- and choose a format for this

2. You may also use a formula like
=DATE(YEAR(B1)+3),MONTH(B1),DAY(B1))

 

[skylinekiller]

The attachment is what I'm trying to accomplish. I wasn't able t
figure out your last post, the formula kept telling me to add anothe
")" My personal email is (e-mail address removed)

Attachment filename: training help.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=52771

 

[Frank Kabel]

Hi
sorry I missed a bracket. Use
B1>DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))