Conditional formatting of date-cell - 2007

[UlfHJensen]

I would like to conditional format cells - in colors - containing a date if
date in cell is before or after TODAY(). This is not - strangely - an option
in the general Highlight date occurring... CF of Excel2007. How do I do this,
then? I have tried greater/less than but it did not work.
Additional question:
I tried one of the possible options (Yesterday) this worked with date format
dd-MM-yy but when I changed format to yy-MM-dd (which I would like to use) it
did not work. How come?
Any help appreciated!

 

[Jacob Skaria]

Select the range cell (say cell A1)

Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format. Enter the formula in the box
below.


'if not today
=AND(ISNUMBER(A1),A1<>TODAY())

'if date greater than today
=AND(ISNUMBER(A1),A1>TODAY())

 

[UlfHJensen]

Hello Jacob,
Much as I appreciate your answer, I cannot ake it work. Am I missing
something?

P.S. On the second question I had I found a cause. Human in origin ;-)

 

[Jacob Skaria]

Try

1. Suppose you have dates in A1:A10. Select the cell/Range (say A1:A10).
Please note that the cell reference A1 mentioned in the formula is the active
cell in the selection. Active cell will have a white background even after
selection

2. From menu Format>Conditional Formatting>

3. For Condition1>Select 'Formula Is' and enter the below formula
'if not today
=AND(ISNUMBER(A1),A1<>TODAY())

'if date greater than today
=AND(ISNUMBER(A1),A1>TODAY())

4. Click Format Button>Pattern and select your color (say Red)

5. Hit OK

PS: If you are using XL2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format. Enter the formula in the box below.

 

[UlfHJensen]

Hello again.
I thought I was experienced in my knowledge of Excel, but it appears not. I
have really tried and tweaked your formula e.a. but I cannot make your
suggestion work.
If I understood you correctly the "A1" in your formula is the cell reference?
I tried under the above assumption and it only turned my cell [red] in the
event the date I entered was today. Hmmm...

 

[Jacob Skaria]

You must be having some format issues...Short-cut to enter todays date is

Hit Ctrl and ; together

--
Jacob (MVP - Excel)

Hello again.
I thought I was experienced in my knowledge of Excel, but it appears not. I
have really tried and tweaked your formula e.a. but I cannot make your
suggestion work.
If I understood you correctly the "A1" in your formula is the cell reference?
I tried under the above assumption and it only turned my cell [red] in the
event the date I entered was today. Hmmm...
--
Best regards
Ulf

Try

1. Suppose you have dates in A1:A10. Select the cell/Range (say A1:A10).
Please note that the cell reference A1 mentioned in the formula is the active
cell in the selection. Active cell will have a white background even after
selection

2. From menu Format>Conditional Formatting>

3. For Condition1>Select 'Formula Is' and enter the below formula
'if not today
=AND(ISNUMBER(A1),A1<>TODAY())

'if date greater than today
=AND(ISNUMBER(A1),A1>TODAY())

4. Click Format Button>Pattern and select your color (say Red)

5. Hit OK

PS: If you are using XL2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format. Enter the formula in the box below.