Conditional Formatting & Dates?
 

This is probably a really simple question, but I can't get my head around it.

In one Column I have a list of dates, and above it I have a calendar
template. I want to set up conditional formatting so that if a date appears
in the list, the cell of that date on the calendar is highlighted yellow.

I've used:
=$B$51:$B$105=DATE(2005,1,7)
I put this in the cell of 7th Jan 05 (not US format) This worked great and
the cell was highlighted. But when I try it with any other date after 9th
Jan 2005 it fails!
ie.
=$B$51:$B$105=DATE(2005,2,4) - This formula to highlight on 4th Feb doesn't
work!

What am I doing wrong?

All help is greatly appreciated!!

Thanks,

Nat

Nat,

Select B51:B105 and use a formula of

=B51=DATE(2005,1,7)

Excel will adjust the formula in each cell to that cell ref, which is what
you want.

Also better to put the compare date in a cell, say C1, and test that

=B51=$C$1

the C1 has to be absolute so that Excel does NOT update that for each cell
in the selected range.

--
HTH

Bob Phillips

"Nat" wrote in message
...
This is probably a really simple question, but I can't get my head around
it.

In one Column I have a list of dates, and above it I have a calendar
template. I want to set up conditional formatting so that if a date
appears
in the list, the cell of that date on the calendar is highlighted yellow.

I've used:
=$B$51:$B$105=DATE(2005,1,7)
I put this in the cell of 7th Jan 05 (not US format) This worked great
and
the cell was highlighted. But when I try it with any other date after 9th
Jan 2005 it fails!
ie.
=$B$51:$B$105=DATE(2005,2,4) - This formula to highlight on 4th Feb
doesn't
work!

What am I doing wrong?

All help is greatly appreciated!!

Thanks,

Nat

Hi Bob,

I'm a little confused and not sure if I was clear enough.
The list of dates from B51-B105 changes depending on data entered on a
different sheet, so the dates in that list aren't the same every time. In my
calendar, I want the cell to search for it's date in that list and if it's
there, highlight in yellow.

It works great for Jan 1 - 9 using conditional formatting on that cell in my
calendar with Formula Is =$B$51:$B$105=DATE(2005,1,3) but when I update the
date for anything after that it doesn't work.

I thought I was just adding the dates in wrong somehow??

Thanks, hope this makes sense :o)

Nat

"Bob Phillips" wrote:

Nat,

Select B51:B105 and use a formula of

=B51=DATE(2005,1,7)

Excel will adjust the formula in each cell to that cell ref, which is what
you want.

Also better to put the compare date in a cell, say C1, and test that

=B51=$C$1

the C1 has to be absolute so that Excel does NOT update that for each cell
in the selected range.

--
HTH

Bob Phillips

"Nat" wrote in message
...
This is probably a really simple question, but I can't get my head around
it.

In one Column I have a list of dates, and above it I have a calendar
template. I want to set up conditional formatting so that if a date
appears
in the list, the cell of that date on the calendar is highlighted yellow.

I've used:
=$B$51:$B$105=DATE(2005,1,7)
I put this in the cell of 7th Jan 05 (not US format) This worked great
and
the cell was highlighted. But when I try it with any other date after 9th
Jan 2005 it fails!
ie.
=$B$51:$B$105=DATE(2005,2,4) - This formula to highlight on 4th Feb
doesn't
work!

What am I doing wrong?

All help is greatly appreciated!!

Thanks,

Nat

Perhaps just another interp / play ..

Assume the calendar dates are in B51:B105
(From: 01-Jan-05 till 24-Feb-05)

And you have a list of 5 dates in say, B107:B111

07-Jan-05
09-Jan-05
11-Jan-05
23-Feb-05
20-Feb-05

Select B51:B105

Click Format Conditional Formatting
Under Condition 1, put it as
Formula is:
=ISNUMBER(MATCH(B51,$B$107:$B$111,0))
Click Format button Patterns tab Yellow OK
Click OK at the main dialog

The above will colour the 5 cells within B51:B105
with dates matching those within B107:B111

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Nat" wrote in message
...
This is probably a really simple question, but I can't get my head around
it.

In one Column I have a list of dates, and above it I have a calendar
template. I want to set up conditional formatting so that if a date
appears
in the list, the cell of that date on the calendar is highlighted yellow.

I've used:
=$B$51:$B$105=DATE(2005,1,7)
I put this in the cell of 7th Jan 05 (not US format) This worked great
and
the cell was highlighted. But when I try it with any other date after 9th
Jan 2005 it fails!
ie.
=$B$51:$B$105=DATE(2005,2,4) - This formula to highlight on 4th Feb
doesn't
work!

What am I doing wrong?

All help is greatly appreciated!!

Thanks,

Nat

Nat, I've just posted another interp / play for you to try out. See whether
that's on ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

OK, maybe select all the cells to compare, say C1:c10 and try a formula of

=COUNTIF($B$51:$B$105,C1)0

--
HTH

Bob Phillips

"Nat" wrote in message
...
Hi Bob,

I'm a little confused and not sure if I was clear enough.
The list of dates from B51-B105 changes depending on data entered on a
different sheet, so the dates in that list aren't the same every time. In
my
calendar, I want the cell to search for it's date in that list and if it's
there, highlight in yellow.

It works great for Jan 1 - 9 using conditional formatting on that cell in
my
calendar with Formula Is =$B$51:$B$105=DATE(2005,1,3) but when I update
the
date for anything after that it doesn't work.

I thought I was just adding the dates in wrong somehow??

Thanks, hope this makes sense :o)

Nat

"Bob Phillips" wrote:

Nat,

Select B51:B105 and use a formula of

=B51=DATE(2005,1,7)

Excel will adjust the formula in each cell to that cell ref, which is
what
you want.

Also better to put the compare date in a cell, say C1, and test that

=B51=$C$1

the C1 has to be absolute so that Excel does NOT update that for each
cell
in the selected range.

--
HTH

Bob Phillips

"Nat" wrote in message
...
This is probably a really simple question, but I can't get my head
around
it.

In one Column I have a list of dates, and above it I have a calendar
template. I want to set up conditional formatting so that if a date
appears
in the list, the cell of that date on the calendar is highlighted
yellow.

I've used:
=$B$51:$B$105=DATE(2005,1,7)
I put this in the cell of 7th Jan 05 (not US format) This worked
great
and
the cell was highlighted. But when I try it with any other date after
9th
Jan 2005 it fails!
ie.
=$B$51:$B$105=DATE(2005,2,4) - This formula to highlight on 4th Feb
doesn't
work!

What am I doing wrong?

All help is greatly appreciated!!

Thanks,

Nat