Help!

Conditional formatting of date-cell - 2007

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions RSS
Next:  How to write personal letters US President search..  
Author Message
UlfHJensen
External


Since: Apr 21, 2010
Posts: 6



PostPosted: Wed Apr 21, 2010 5:39 am    Post subject: Conditional formatting of date-cell - 2007
Archived from groups: microsoft>public>excel>misc (more info?)

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
Back to top
Jacob Skaria
External


Since: Mar 04, 2009
Posts: 568



PostPosted: Wed Apr 21, 2010 6:14 am    Post subject: RE: Conditional formatting of date-cell - 2007 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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


--
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
Back to top
UlfHJensen
External


Since: Apr 21, 2010
Posts: 6



PostPosted: Wed Apr 21, 2010 7:01 am    Post subject: RE: Conditional formatting of date-cell - 2007 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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


"Jacob Skaria" wrote:

> 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())
>
>
> --
> 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
Back to top
Jacob Skaria
External


Since: Mar 04, 2009
Posts: 568



PostPosted: Wed Apr 21, 2010 7:05 am    Post subject: RE: Conditional formatting of date-cell - 2007 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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.

--
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 Wink
> --
> Best regards
> Ulf
>
>
> "Jacob Skaria" wrote:
>
> > 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())
> >
> >
> > --
> > 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
Back to top
UlfHJensen
External


Since: Apr 21, 2010
Posts: 6



PostPosted: Wed Apr 21, 2010 8:46 am    Post subject: RE: Conditional formatting of date-cell - 2007 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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" wrote:

> 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.
>
> --
> 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 Wink
> > --
> > Best regards
> > Ulf
> >
> >
> > "Jacob Skaria" wrote:
> >
> > > 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())
> > >
> > >
> > > --
> > > 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
Back to top
Jacob Skaria
External


Since: Mar 04, 2009
Posts: 568



PostPosted: Wed Apr 21, 2010 9:15 am    Post subject: RE: Conditional formatting of date-cell - 2007 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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

Hit Ctrl and ; together

--
Jacob (MVP - Excel)


"UlfHJensen" wrote:

> 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" wrote:
>
> > 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.
> >
> > --
> > 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 Wink
> > > --
> > > Best regards
> > > Ulf
> > >
> > >
> > > "Jacob Skaria" wrote:
> > >
> > > > 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())
> > > >
> > > >
> > > > --
> > > > 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
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum