Holidays

[Guest]

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

 

[Roger Govier]

Hi Jerry

Create a list of cells with all of your holiday dates in them, say in
H1:H10
Insert>Name>Define>Holidays 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.

 

[Guest]

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