How to change data in a cell, based on the date

[Rangerbob86]

Hey. Heres what I want to do:

I have a worksheet that has dates of when I need to do things, kind of like a calender. Is there a way to change the color of the text when the date approaches? Please help me!

[SteveG]

Rangerbob86,

You can use conditional formatting. Select a cell where your dates are say
A1 then click on the Format menu. Select Conditional Formatting. Change the Cell value is option to Formula is: For Condition one try this if your date is less than 5 days from today.

=(A1-TODAY())<5

Select Format>Patterns and select your color. Click OK and OK. Copy the format to the other cells in your range.

HTH

Steve

[Rangerbob86]

hey thanks! that worked. Now, is there also a way to get the formatting to go away, once the date has passed?

[SteveG]

This should work.

=AND((A1-TODAY())<5,(A1-TODAY())>=0)

Cheers,

Steve

[Rangerbob86]

great! so that works....but is there a way to copy and paste the conditional formatting to each of my cells that contain dates? because i think in your formula you have to change A1 to whatever cell it is, right? so do I have to do each one manually?

[SteveG]

You can use the Format Painter. Select the first cell in your range. Click on the Format painter (looks like a paint brush). While holding down the left mouse button, highlight the rest of your range and release. Or you could go to the first cell in your range (assuming it is a column) move the mouse over the lower right hand corner of the first cell until it changes to a black cross and double click. That will copy it down the range.

Steve