Holidays

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
 
Back
Top