Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count if the the critera is met then half it | Excel Discussion (Misc queries) | |||
Number of Week Days _including_ Holidays | Excel Worksheet Functions | |||
Generating business days in a calendar month, EXCLUDING holidays | Excel Worksheet Functions | |||
Formula - Excluding weekends & holidays | Excel Worksheet Functions | |||
Skip the Holidays 2 | Excel Discussion (Misc queries) |