Conditional formatting of date-cell - 2007


U

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!
 
Ad

Advertisements

J

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())
 
U

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 ;-)
 
J

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.
 
U

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...
 
Ad

Advertisements

J

Jacob Skaria

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

Hit Ctrl and ; together

--
Jacob (MVP - Excel)


UlfHJensen said:
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


Jacob Skaria said:
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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top