Excel Wizards: help with conditional formatting and dates?

[NikPreviousAcct]

Thanks for taking the time to read the thread.

In a cell, there's a date. It's going to be, say, today's date. I need the cell to be highlighted a certain color if today's date is a certain number of days after that.

Example:

Employee gets licensed for something. That license expires in 2 years. The date he aquired the license goes into the cell and HR wants to be alerted 30 days before his license expires. Maybe it's that I'm tired or just not in a working mood right now but I'm having quite a bit of trouble with getting the formula down.

Ultimately, I'd like to have 2 conditions. One will highlight the cell yellow if it's 701 days past the date in the cell (30 days until expiration) and another to turn the cell red if it's 731 days past (expired).

Ideas please?

 

[screw3d]

This is the best I can come up with. I'm not Excel wiz

 

[theknight571]

Here's what I came up with... it's similar to screw3d's solution, but doesn't need the extra column.

Unfortunately I cannot upload the file anywhere so I'll try to make it clear here what I've done.

Condition 1:
Cell Value is | greater than | =NOW()-700 | Background Color = Green

Condition 2:
Cell Value is | between | =NOW()-701 | =NOW()-730 | Background Color = Yellow

Condition 3:
Cell Value is | less than | =NOW()-731 | Background Color = Red


Basically what is says is:

If the date in the current cell is more recent than 700 days ago, turn the cell green.

If the date in the current cell is between 700 days ago and 730 days ago, turn the cell yellow.

If the date in the current cell is later than 730 days ago, turn the cell red.


The NOW() function returns the current date/time.

Edit: I just noticed that screw3d used the TODAY() function, that should work as well as the NOW() function.


If you'd like I can email the file once I get home from work.

- TK

 

[NikPreviousAcct]

Awesome, thanks for the help theknight571. I tried a couple times to download screw3d's file but it just wouldn't come through for some reason. Thanks for your efforts anyway screw3d.