highlighting cells which have dates later than...

I would like to highlight date cells in a different color which occur for
example after 1/1/2000.

The dates will be all on a colum, some before 1/1/2000 and some after.

In addition to just highlighting the cell itself, is there a way to
highlight adjacent cells.

Any help or pointers would be greatly appreciated.

Regards, Rory

Use format>conditional formatting (after selecting the cell(s)), formula is
and

=A1>DATE(2000,1,1)

click the format button the change the format
to include adjacent cells refer to the same formula but use absolute
references =$A$1>DATE(2000,1,1)

where in this case A1 would be the cell you want to highlight

Regards,

Peo Sjoblom


"Rory Carroll" wrote:

> I would like to highlight date cells in a different color which occur for
> example after 1/1/2000.
>
> The dates will be all on a colum, some before 1/1/2000 and some after.
>
> In addition to just highlighting the cell itself, is there a way to
> highlight adjacent cells.
>
> Any help or pointers would be greatly appreciated.
>
> Regards, Rory

No joy,

This is what I did.

-Highlighted the column (don't think this is necessary)
-clicked fotmat, conditional format
-in condition 1 I selected "formula is"
-Used the cell selection too to select which cells to use (in my case :
"=$D$3:$D$17")
-clicked the same button again (to signal my selection end)
-put in ">DATE(2004,7,1)" to give me the line "=$D$3:$D$17>DATE(2004,7,1)"
-clicked format and gave it a color

The cells containg information like this:
Sep 1 2004 4:57AM
Sep 2 2004 3:59AM
Jun 28 2004 8:16AM
....etc

So I expected the sep cells to be highlighted.

Thanks for your input

Rory



"Peo Sjoblom" wrote:

> Use format>conditional formatting (after selecting the cell(s)), formula is
> and
>
> =A1>DATE(2000,1,1)
>
> click the format button the change the format
> to include adjacent cells refer to the same formula but use absolute
> references =$A$1>DATE(2000,1,1)
>
> where in this case A1 would be the cell you want to highlight
>
> Regards,
>
> Peo Sjoblom
>
>
> "Rory Carroll" wrote:
>
> > I would like to highlight date cells in a different color which occur for
> > example after 1/1/2000.
> >
> > The dates will be all on a colum, some before 1/1/2000 and some after.
> >
> > In addition to just highlighting the cell itself, is there a way to
> > highlight adjacent cells.
> >
> > Any help or pointers would be greatly appreciated.
> >
> > Regards, Rory

Highlight D3:D17 and with the activecell in D3
use Peo's formula (slightly modified)

=D3>DATE(2000,1,1)

Excel will adjust the formula for each cell in the selection.

Rory Carroll wrote:
>
> No joy,
>
> This is what I did.
>
> -Highlighted the column (don't think this is necessary)
> -clicked fotmat, conditional format
> -in condition 1 I selected "formula is"
> -Used the cell selection too to select which cells to use (in my case :
> "=$D$3:$D$17")
> -clicked the same button again (to signal my selection end)
> -put in ">DATE(2004,7,1)" to give me the line "=$D$3:$D$17>DATE(2004,7,1)"
> -clicked format and gave it a color
>
> The cells containg information like this:
> Sep 1 2004 4:57AM
> Sep 2 2004 3:59AM
> Jun 28 2004 8:16AM
> ...etc
>
> So I expected the sep cells to be highlighted.
>
> Thanks for your input
>
> Rory
>
> "Peo Sjoblom" wrote:
>
> > Use format>conditional formatting (after selecting the cell(s)), formula is
> > and
> >
> > =A1>DATE(2000,1,1)
> >
> > click the format button the change the format
> > to include adjacent cells refer to the same formula but use absolute
> > references =$A$1>DATE(2000,1,1)
> >
> > where in this case A1 would be the cell you want to highlight
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> > "Rory Carroll" wrote:
> >
> > > I would like to highlight date cells in a different color which occur for
> > > example after 1/1/2000.
> > >
> > > The dates will be all on a colum, some before 1/1/2000 and some after.
> > >
> > > In addition to just highlighting the cell itself, is there a way to
> > > highlight adjacent cells.
> > >
> > > Any help or pointers would be greatly appreciated.
> > >
> > > Regards, Rory

--

Dave Peterson

No Joy.

Could it be that it doesn't understand the cell as "Sep 1 2004 4:57AM"
(without the quotes)?

what I did:
-selected cells D3 down as far as D17 (D3 is shown in the menu bar
(activecell I guess))
-went to format, conditional formatting
-put in the formula =D3>DATE(2004,7,1)
-changed the format to bold
-clicked ok
-expected the cell Jun 28 2004 8:16AM to not be in bold, but all the cells
were in bold

here is the colum D from row 3 to 17

Sep 3 2004 12:15AM
Sep 3 2004 3:55AM
Sep 1 2004 4:57AM
Sep 2 2004 3:59AM
Sep 3 2004 8:45AM
Sep 1 2004 1:21AM
Jun 28 2004 8:16AM
Sep 3 2004 6:42AM
Sep 5 2004 1:14PM
Aug 13 2004 5:29AM
Sep 2 2004 8:48AM
Sep 3 2004 5:58AM
Aug 3 2004 12:49AM
Sep 3 2004 6:02AM
Sep 3 2004 12:22PM

Thanks,

Rory

"Dave Peterson" wrote:

> Highlight D3:D17 and with the activecell in D3
> use Peo's formula (slightly modified)
>
> =D3>DATE(2000,1,1)
>
> Excel will adjust the formula for each cell in the selection.
>
> Rory Carroll wrote:
> >
> > No joy,
> >
> > This is what I did.
> >
> > -Highlighted the column (don't think this is necessary)
> > -clicked fotmat, conditional format
> > -in condition 1 I selected "formula is"
> > -Used the cell selection too to select which cells to use (in my case :
> > "=$D$3:$D$17")
> > -clicked the same button again (to signal my selection end)
> > -put in ">DATE(2004,7,1)" to give me the line "=$D$3:$D$17>DATE(2004,7,1)"
> > -clicked format and gave it a color
> >
> > The cells containg information like this:
> > Sep 1 2004 4:57AM
> > Sep 2 2004 3:59AM
> > Jun 28 2004 8:16AM
> > ...etc
> >
> > So I expected the sep cells to be highlighted.
> >
> > Thanks for your input
> >
> > Rory
> >
> > "Peo Sjoblom" wrote:
> >
> > > Use format>conditional formatting (after selecting the cell(s)), formula is
> > > and
> > >
> > > =A1>DATE(2000,1,1)
> > >
> > > click the format button the change the format
> > > to include adjacent cells refer to the same formula but use absolute
> > > references =$A$1>DATE(2000,1,1)
> > >
> > > where in this case A1 would be the cell you want to highlight
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > >
> > > "Rory Carroll" wrote:
> > >
> > > > I would like to highlight date cells in a different color which occur for
> > > > example after 1/1/2000.
> > > >
> > > > The dates will be all on a colum, some before 1/1/2000 and some after.
> > > >
> > > > In addition to just highlighting the cell itself, is there a way to
> > > > highlight adjacent cells.
> > > >
> > > > Any help or pointers would be greatly appreciated.
> > > >
> > > > Regards, Rory
>
> --
>
> Dave Peterson
>

Ok, I'm almost sure that the data there is not in date format, that it has
been pulled from data (ie sept is sep, then there is the two spaces after it.
I'm posting a new post in programming)

I'll now need to change sep 1 2004 6:42AM
to
1/9/2004

I'll need a macro or something?

regards, Rory


"Rory Carroll" wrote:

> No Joy.
>
> Could it be that it doesn't understand the cell as "Sep 1 2004 4:57AM"
> (without the quotes)?
>
> what I did:
> -selected cells D3 down as far as D17 (D3 is shown in the menu bar
> (activecell I guess))
> -went to format, conditional formatting
> -put in the formula =D3>DATE(2004,7,1)
> -changed the format to bold
> -clicked ok
> -expected the cell Jun 28 2004 8:16AM to not be in bold, but all the cells
> were in bold
>
> here is the colum D from row 3 to 17
>
> Sep 3 2004 12:15AM
> Sep 3 2004 3:55AM
> Sep 1 2004 4:57AM
> Sep 2 2004 3:59AM
> Sep 3 2004 8:45AM
> Sep 1 2004 1:21AM
> Jun 28 2004 8:16AM
> Sep 3 2004 6:42AM
> Sep 5 2004 1:14PM
> Aug 13 2004 5:29AM
> Sep 2 2004 8:48AM
> Sep 3 2004 5:58AM
> Aug 3 2004 12:49AM
> Sep 3 2004 6:02AM
> Sep 3 2004 12:22PM
>
> Thanks,
>
> Rory
>
> "Dave Peterson" wrote:
>
> > Highlight D3:D17 and with the activecell in D3
> > use Peo's formula (slightly modified)
> >
> > =D3>DATE(2000,1,1)
> >
> > Excel will adjust the formula for each cell in the selection.
> >
> > Rory Carroll wrote:
> > >
> > > No joy,
> > >
> > > This is what I did.
> > >
> > > -Highlighted the column (don't think this is necessary)
> > > -clicked fotmat, conditional format
> > > -in condition 1 I selected "formula is"
> > > -Used the cell selection too to select which cells to use (in my case :
> > > "=$D$3:$D$17")
> > > -clicked the same button again (to signal my selection end)
> > > -put in ">DATE(2004,7,1)" to give me the line "=$D$3:$D$17>DATE(2004,7,1)"
> > > -clicked format and gave it a color
> > >
> > > The cells containg information like this:
> > > Sep 1 2004 4:57AM
> > > Sep 2 2004 3:59AM
> > > Jun 28 2004 8:16AM
> > > ...etc
> > >
> > > So I expected the sep cells to be highlighted.
> > >
> > > Thanks for your input
> > >
> > > Rory
> > >
> > > "Peo Sjoblom" wrote:
> > >
> > > > Use format>conditional formatting (after selecting the cell(s)), formula is
> > > > and
> > > >
> > > > =A1>DATE(2000,1,1)
> > > >
> > > > click the format button the change the format
> > > > to include adjacent cells refer to the same formula but use absolute
> > > > references =$A$1>DATE(2000,1,1)
> > > >
> > > > where in this case A1 would be the cell you want to highlight
> > > >
> > > > Regards,
> > > >
> > > > Peo Sjoblom
> > > >
> > > >
> > > > "Rory Carroll" wrote:
> > > >
> > > > > I would like to highlight date cells in a different color which occur for
> > > > > example after 1/1/2000.
> > > > >
> > > > > The dates will be all on a colum, some before 1/1/2000 and some after.
> > > > >
> > > > > In addition to just highlighting the cell itself, is there a way to
> > > > > highlight adjacent cells.
> > > > >
> > > > > Any help or pointers would be greatly appreciated.
> > > > >
> > > > > Regards, Rory
> >
> > --
> >
> > Dave Peterson
> >

Maybe.

But I'd try this first.

Are all those "dates" in one column?
And do you need to keep the time portion?

If yes, then insert a new column to the right of that date column.
select the date column
data|text to columns
fixedwidth and erase any lines that excel guessed
But put a line in front of the time

Tell the wizard that the first portion is a date (mdy) and the second portion is
general.

Plop the results in the original column and the new column to the right.

But the times aren't times yet.

select that times column
edit|replace
what: AM (just AM--nothing else)
with: _AM (underscore represents a spacebar)
replace all

do the same with PM to _PM (SpaceBar PM)

And format your dates & times the way you want.

If you don't need the time, then tell the wizard to skip (do not import) that
field.




Rory Carroll wrote:
>
> Ok, I'm almost sure that the data there is not in date format, that it has
> been pulled from data (ie sept is sep, then there is the two spaces after it.
> I'm posting a new post in programming)
>
> I'll now need to change sep 1 2004 6:42AM
> to
> 1/9/2004
>
> I'll need a macro or something?
>
> regards, Rory
>
> "Rory Carroll" wrote:
>
> > No Joy.
> >
> > Could it be that it doesn't understand the cell as "Sep 1 2004 4:57AM"
> > (without the quotes)?
> >
> > what I did:
> > -selected cells D3 down as far as D17 (D3 is shown in the menu bar
> > (activecell I guess))
> > -went to format, conditional formatting
> > -put in the formula =D3>DATE(2004,7,1)
> > -changed the format to bold
> > -clicked ok
> > -expected the cell Jun 28 2004 8:16AM to not be in bold, but all the cells
> > were in bold
> >
> > here is the colum D from row 3 to 17
> >
> > Sep 3 2004 12:15AM
> > Sep 3 2004 3:55AM
> > Sep 1 2004 4:57AM
> > Sep 2 2004 3:59AM
> > Sep 3 2004 8:45AM
> > Sep 1 2004 1:21AM
> > Jun 28 2004 8:16AM
> > Sep 3 2004 6:42AM
> > Sep 5 2004 1:14PM
> > Aug 13 2004 5:29AM
> > Sep 2 2004 8:48AM
> > Sep 3 2004 5:58AM
> > Aug 3 2004 12:49AM
> > Sep 3 2004 6:02AM
> > Sep 3 2004 12:22PM
> >
> > Thanks,
> >
> > Rory
> >
> > "Dave Peterson" wrote:
> >
> > > Highlight D3:D17 and with the activecell in D3
> > > use Peo's formula (slightly modified)
> > >
> > > =D3>DATE(2000,1,1)
> > >
> > > Excel will adjust the formula for each cell in the selection.
> > >
> > > Rory Carroll wrote:
> > > >
> > > > No joy,
> > > >
> > > > This is what I did.
> > > >
> > > > -Highlighted the column (don't think this is necessary)
> > > > -clicked fotmat, conditional format
> > > > -in condition 1 I selected "formula is"
> > > > -Used the cell selection too to select which cells to use (in my case :
> > > > "=$D$3:$D$17")
> > > > -clicked the same button again (to signal my selection end)
> > > > -put in ">DATE(2004,7,1)" to give me the line "=$D$3:$D$17>DATE(2004,7,1)"
> > > > -clicked format and gave it a color
> > > >
> > > > The cells containg information like this:
> > > > Sep 1 2004 4:57AM
> > > > Sep 2 2004 3:59AM
> > > > Jun 28 2004 8:16AM
> > > > ...etc
> > > >
> > > > So I expected the sep cells to be highlighted.
> > > >
> > > > Thanks for your input
> > > >
> > > > Rory
> > > >
> > > > "Peo Sjoblom" wrote:
> > > >
> > > > > Use format>conditional formatting (after selecting the cell(s)), formula is
> > > > > and
> > > > >
> > > > > =A1>DATE(2000,1,1)
> > > > >
> > > > > click the format button the change the format
> > > > > to include adjacent cells refer to the same formula but use absolute
> > > > > references =$A$1>DATE(2000,1,1)
> > > > >
> > > > > where in this case A1 would be the cell you want to highlight
> > > > >
> > > > > Regards,
> > > > >
> > > > > Peo Sjoblom
> > > > >
> > > > >
> > > > > "Rory Carroll" wrote:
> > > > >
> > > > > > I would like to highlight date cells in a different color which occur for
> > > > > > example after 1/1/2000.
> > > > > >
> > > > > > The dates will be all on a colum, some before 1/1/2000 and some after.
> > > > > >
> > > > > > In addition to just highlighting the cell itself, is there a way to
> > > > > > highlight adjacent cells.
> > > > > >
> > > > > > Any help or pointers would be greatly appreciated.
> > > > > >
> > > > > > Regards, Rory
> > >
> > > --
> > >
> > > Dave Peterson
> > >

--

Dave Peterson