cell conditional format with dates/colors

[skylinekiller]

I have attached the file to better explain myself

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 lets sa
that I have a date of last trained on 15 Feb 2003 and the due date i
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,
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

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

 

[DDM]

Skylinekiller,

Question 1, formatting for Cell E5.

Format > Conditional Formatting.

Condition 1: Formula is =TODAY()>=E5-30
Condition 2: Formula is =TODAY()>=E5-90
Condition 3: Formula is =TODAY()<E5-90

Copy formatting down the column.

Question 2, automatic entry of training due date.

In Cell E5: =D5+365*C5. Copy down the column.

 

[skylinekiller]

Thank you for your help. It worked perfectly and you were quic
responding. I really appreciate it. I might be back with questions o
graphs. I hope you don't get irritated. I'll try to read my ebook
first

 

[skylinekiller]

On my worksheet I ahve a cell which turns colors when the training dat
gets within 90 days, 30 days, and over 90 days. My question is, I wa
wondering if it's possible that once that cell falls with in the 9
days before training I want Excel to automaticall send an email or som
type of response. Is this possible