help with experation date color coding

**MISTISAKITTY** · 07-15-2009, 01:05 AM

I know this is probably really simple, but I am trying to create a spreadsheet that as the dates change the BG color in the cell will change as it gets closer to the experation date.

B4 is the employees starting date
C4 the date the employee stops working. 90 days after this date they need to redo their training.
So D4 is the experation date of their training.

During the first 60 days I want it to be one color, then the last thirty days to be another color, and then after the 90th day for it to turn red as it is expired.

I have about 300 employee's to do this for.

Also, In D4 i have a formula that adds 90 days to the experation date in C4, could this be causing a problem?

I've been through the forums as well as others, and everything i'm trying doesn't seem to work. Please help.

A million thanks.

**mewingkitty** · 07-15-2009, 01:11 AM

An example workbook would be really handy.

I came up with something like this at work a while ago actually... I think... You're looking for something that when you open it it'll take todays date (=today()) and compare it to dates where people are scheduled for things?

Conditional formatting is the way you wanna go.

Make up some names and replace the real ones with them, fudge the numbers or whatever, but if we can actually see what all is in there we'll get ya fixed up much faster.

p.s.
mew.

**MISTISAKITTY** · 07-15-2009, 03:17 AM

yes!! that is pretty much what i want!

Here is pretty much what I'm working on, it's real simple, and i was using the conditional formatting and it just wasn't working. if you could please help i'd reallllly appreciate it

**zbor** · 07-15-2009, 04:26 AM

Go to D4 cell, choose conditional formating and in Use formula.. write:

=TODAY()>$D5-30

30 is number of days you want collor to appear.

For example, in deafult you put green
then in conditional format for number 30 you put yellow
and same rule only change number i.e. =TODAY()>$D5-7 choose red
and for example after Expiration day use =TODAY()>=$D4 and choose black background with white letters