Conditional formatting of date-cell - 2007
 

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!
--
Best regards
Ulf

Conditional formatting of date-cell - 2007
 

Select the range cell (say cell A1)

Goto Home tabStylesConditional FormattingManage rulesNew ruleUse 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),A1TODAY())


--
Jacob (MVP - Excel)


"UlfHJensen" wrote:

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!
--
Best regards
Ulf

Conditional formatting of date-cell - 2007
 

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 ;-)
--
Best regards
Ulf


"Jacob Skaria" wrote:

Select the range cell (say cell A1)

Goto Home tabStylesConditional FormattingManage rulesNew ruleUse 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),A1TODAY())


--
Jacob (MVP - Excel)


"UlfHJensen" wrote:

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!
--
Best regards
Ulf

Conditional formatting of date-cell - 2007
 

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 FormatConditional Formatting

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

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

4. Click Format ButtonPattern and select your color (say Red)

5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format. Enter the formula in the box below.

--
Jacob (MVP - Excel)


"UlfHJensen" wrote:

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 ;-)
--
Best regards
Ulf


"Jacob Skaria" wrote:

Select the range cell (say cell A1)

Goto Home tabStylesConditional FormattingManage rulesNew ruleUse 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),A1TODAY())


--
Jacob (MVP - Excel)


"UlfHJensen" wrote:

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!
--
Best regards
Ulf