using date differences to format cell colors

[docdubz07]

Its been quite a while since i have used excel, so sorry if this is an extremely stupid question. Im in the military and am currently putting together a spreadsheet to keep track of other service members training requirements. basically what i am trying to do is this:

say cell A2 has the date 12-05-10 (day month year) how can i make it so based on the current date cell A2 will automatically change color (i.e. green for training that has been completed, yellow for training that will expire within 3 months, and red for training that is expired based on the current date.)

I appologize if this is a stupid question but ive been with the infantry for quite some time and have not worked with computers for several years.

thank you in advance

[teylyn]

Hi docdubz07,

welcome to the forum.

You can do that with conditional formatting and I can show you how, but I'm not quite sure how to distinguish between "training that has been completed" and "training that is expired". How do these two relate to the current date? Is there info in another cell that needs to be taken into consideration?

Maybe you could upload some sample data that looks like your original spreadsheet and mock up what you'd like to see. Then explain the rules and we'll take it from there.

To upload a file, click Go Advanced below the Quick Reply box and then the paper clip icon.

cheers

[docdubz07]

thank you and I appologize for being vague

basically we have annually required training and for each service member there are dates in several difference cells corresponding to each training class. i would like to compare those dates to the current date which i put in its own cell. if the date of completion for the training is past the current date i would like it to highlight red, if the same date is 3 or less months away from the current date i would like it to highlight yellow and if the date has more than 3 months before expiring i would like it to highlight green.

i am trying to make it more efficient on time spendature to go over the training records of my sailors.

thank you for your reply

[teylyn]

Docdubz,

do you require the 3 months to be on the dot, or can we just say 90 days? If 90 days is acceptable, and using the sample sheet you attached, you can do this:

- highlight B6 to C14
- click Format - Conditional Formatting
- add these three rules in this order:

1st rule - Formula is
=B6<TODAY()
set to red

2nd rule - Formula is
=B6>(TODAY()+90)
set to green

3rd rule - Formula is
=B6<=(TODAY()+90)
set to yellow

Note that there must be no $ signs in the cell address. This way it will automatically adjust and evaluate the current cell.

hth

[docdubz07]

that helped immensely thank you!