Conditional formatting with dates

Met Fan

Board Regular
Joined
Jul 1, 2004
Messages
185
I have a column with dates in them and want to change the color of the cells when they reach a certain date. I set up the formatting so that it changes if the cell value is less than or equal to todays date (referencing =Today() in a seperate cell. This works fine, but it also formats any blank cells. How can I set it up so that the formatting only occurs in the cells with values? I highlighted the entire column when I set up the conditional formatting so that I wouldn't have to keep adding it as I added rows of data.

Thanks.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
formula is in conditional format with this formula
=AND(A1<=$D$1,A1>0)
D1 contains the formaula =TODAY() Conditional formats are volatile (they recalculate everytime a change is made). You shouldn't use more than what you need. Volatile items in your spreadsheet will slow calculation time down.
 

Met Fan

Board Regular
Joined
Jul 1, 2004
Messages
185
Now everything is formatted even the blank cells. I want the conditional formatting to apply only to the cells with data.
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
Here is an example of what should have happened. The formulas in column B are just there so you can see the results of the formula. I'm not sure why it didn't work for you. I did a test as always before posting. You do have the dollar signs in the proper place? The results I got are the results you wanted right? If not help me understand your problem a little better.
Book4
ABCD
110/8/2006TRUE10/20/2006
210/9/2006TRUE
310/10/2006TRUE
410/11/2006TRUE
510/12/2006TRUE
610/13/2006TRUE
710/14/2006TRUE
8FALSE
910/16/2006TRUE
1010/17/2006TRUE
1110/18/2006TRUE
1210/19/2006TRUE
1310/20/2006TRUE
1410/21/2006FALSE
15FALSE
1610/23/2006FALSE
1710/24/2006FALSE
1810/25/2006FALSE
Sheet2
 

moneytastesbad

Board Regular
Joined
Aug 28, 2006
Messages
106
I copied and pasted the formula suggested, and pasted it into conditional formating.

Worked for me.

Dates before today are highlighted, Dates after today are not, Blanks are not
 

Met Fan

Board Regular
Joined
Jul 1, 2004
Messages
185
I'm sorry it took me so long to get around to responding. this works fine now, when I was entering the conditional formatting I did not change it to Formula =, it was still on Cell = and that is why it didn't work.

Thanks alot for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,093
Messages
5,545,919
Members
410,712
Latest member
jhgeorge
Top