Formula to signal approaching date?

[benajah]

Hi all,
I am far from an excel expert but have been flagged at my new job as needing to be the go to guy for our excel task trackers. One formula I have been trying to figure out but have no success is how to signal a cell that contains a date through conditional formatting, as in have it turn red, green, etc when a particular date is approaching. The formula I am trying to figure out would read narratively something like: "when the date in this cell is 10 days from today, it turns green. When it is 5 days from now it turns red." Would anyone have any suggestions?
Essentially I have a spreadsheet that shows due dates for a bunch of tasks for a bunch of projects, and I need to formulate a way for the cells to turn color or some other visual cue as the due dates approach.
Thanks in advance.

[alansidman]

Look at conditional formatting

http://www.techonthenet.com/excel/cells/index.php

[Pete_UK]

Highlight the date cells - assume these are in column D starting from D2 downwards. Then click on Conditional Formatting | New Rule and choose Use a Formula from the bottom of the list. Put this formula in the dialogue box:

=AND($D2<TODAY()-5,$D2>=TODAY()-10)

Then click on the Format button | Fill tab and choose green, then OK your way out.

Repeat for the other condition, but use this formula:

=AND($D2<TODAY(),$D2>=TODAY()-5)

and choose red for the background colour.

Excel will automatically adjust the cell references to suit the cells that were selected beforehand.

Hope this helps.

Pete

[benishiryo]

welcome to the forum, benajah. i'm thinking in another direction, so follow Pete's method but use this formula if that's not what you want.
green
=AND($D2-TODAY()<=10,$D2-TODAY()>=6)

red
=AND($D2-TODAY()<=5,$D2-TODAY()>=1)

it's always best to upload a sample excel file with different scenarios & manually highlight what you wish to see.