Hi,
Hope someone has the answer to this!!
I have a workbook consisting of 12 sheets ( April-March ). Input 01/04/????
into April A1. Column A contains all my drivers names, rows 2 + 3 have day
and date respectively.
The drivers(126 of them) all work a shifted week, so their days off are not
all the same (one driver has Mon/Tue off, another has Wed/Thu off etc.) the
two corresponding cells in the row next to their name are shaded in for
their
days off.
I have managed to get each sheet to corectly display day/date in B2/3 -
AF2/3 from the input into sheet April A1.
What I need Excell to do if possible is to automatically shade in the
drivers days off on each sheet when I create a new calendar for next year.
Many Thanks in advance

I'm having a little difficulty visualizing the format of your sprdsht,
but: you should be able to use the WEEKDAY function in conjunction with
conditional formatting to do what you need. WEEKDAY returns an integer
number from 1 to 7 representing the day in the week for a given date.

Sorry, I didn't think I explained it to well!!
What I have are 12 worksheets Apr - Mar (If a driver has booked leave I put
a 1 in the cells for the time he has booked off) with 126 drivers with
different days off. I have a master copy, where I input 01/04/???? into sheet
Apr a1 and it generates the correct day/date for each month in rows 2 + 3.
What I would like excel to do, as it is a bit of a chore changing the shading
on all 12 sheets to correspond to their days off, is recognise the driver and
his days off and fill in the appropriate cells i.e. Driver1 Sun and Monday.
Hope that explains it a bit better.
Thanks

Example uploaded here.

http://filesfly.com/f/ef55f64c85_0.04MB

"Dave O" wrote:

I'm having a little difficulty visualizing the format of your sprdsht,
but: you should be able to use the WEEKDAY function in conjunction with
conditional formatting to do what you need. WEEKDAY returns an integer
number from 1 to 7 representing the day in the week for a given date.

Got it- thanks for the file, that helped tremendously. I got the
results you're looking for by doing this:

1. Insert new columns B and C.
2. For each driver, populate the days of in B and C using integer
numbers, where 1=Sunday, 2=Monday, etc thru 7=Saturday. In the sample
data, the first three rows look like this:
....A.........B....C
Driver 1....7....1
Driver 2....1....2
Driver 3....4....5

3. Create conditional formatting for cell D1 (that cell is now Day 1
for Driver 1) with this formula:
=OR(WEEKDAY(D$3)=$B4,WEEKDAY(D$3)=$C4)
....and apply the format you desire. Copy that cell and paste it for
the rest of the days in the month and the rest of the drivers.

Let us know how it goes!
Dave O

Thanks Dave, that was just what I was after. Works great.