Holidays
 

I have a spreadsheet used for keeping track of work hours for student help.
I'm using conditional formatting to check for such things as blocks of time
over 4 hours (a no-no), breaks shorter than 30 min., etc. I also have the
date column autofilling (enter first date, all cells below add 1 to the
previous date). I would like to have holiday dates show a gray fill. I can
use conditional formatting like this: If cell value is equal to 38733 (Feb
20) then cell fill is gray. However, I am limited to 3 conditions. This works
for this semester which has 3 Monday holidays so I can set one condition for
each holiday. But I'd like to have a table of holidays and the date cell
would check for a match and turn gray if that date is a holiday. that way I
could just fill the tble with one year's worth of holidays at once. As it now
stands I need to edit the formulae every semester. If it makes any
difference, I'm using Excel 2004 on a Mac.

Thanks,
Jerry

Holidays
 

Hi Jerry

Create a list of cells with all of your holiday dates in them, say in
H1:H10
InsertNameDefineHolidays Refers to =H1:H10

Then mark your block of cells with the dates and apply a single
Conditional Format of Formula is =(COUNTIF(holidays,A1)0
substituting the first cell in your block for A1 in the above formula.


--
Regards

Roger Govier


"Jerry Levinson" <Jerry wrote in
message ...
I have a spreadsheet used for keeping track of work hours for student
help.
I'm using conditional formatting to check for such things as blocks of
time
over 4 hours (a no-no), breaks shorter than 30 min., etc. I also have
the
date column autofilling (enter first date, all cells below add 1 to
the
previous date). I would like to have holiday dates show a gray fill. I
can
use conditional formatting like this: If cell value is equal to 38733
(Feb
20) then cell fill is gray. However, I am limited to 3 conditions.
This works
for this semester which has 3 Monday holidays so I can set one
condition for
each holiday. But I'd like to have a table of holidays and the date
cell
would check for a match and turn gray if that date is a holiday. that
way I
could just fill the tble with one year's worth of holidays at once. As
it now
stands I need to edit the formulae every semester. If it makes any
difference, I'm using Excel 2004 on a Mac.

Thanks,
Jerry

Holidays
 

Thanks, that worked fine...except you left out the last parenthesis in the
conditional formula! If my typos were that easy to figure out, I'd have a
much easier time of this! ;-)

Jerry

"Roger Govier" wrote:

Hi Jerry

Create a list of cells with all of your holiday dates in them, say in
H1:H10
InsertNameDefineHolidays Refers to =H1:H10

Then mark your block of cells with the dates and apply a single
Conditional Format of Formula is =(COUNTIF(holidays,A1)0
substituting the first cell in your block for A1 in the above formula.


--
Regards

Roger Govier