Creating a teaching schedule: Include weekends but exclude school holidays

[themagicstorybag]

Dear all,

I am trying to create a spreadsheet which will automatically calculate the teaching dates for a teacher teaching lessons on a weekly basis. The spreadsheet should include weekends, as some teaching is taught on Sat/Sun but exclude school holidays. (It would be nice if it could copy the times/students/locations as well – these are the same each week) E.g,.:

A Teacher teaches students on a weekly basis on Mondays:

02/07/2012 15:00-15:50/ Anna/room 501
15:30-16:00/Jack/room 501
16:00-16:30/Joe/room501
09/07/2012 15:00-15:50/ Anna/room 501
15:30-16:00/Jack/room 501
16:00-16:30/Joe/room501

16/07/2012 (HOLIDAY: 16/07/2012-20/07/2012), so no lessons
23/07/2012 15:00-15:50/ Anna/room 501
15:30-16:00/Jack/room 501
16:00-16:30/Joe/room501

What I need the spreadsheet to do is to automatically generate the dates/schedule and skip to the next lesson date if there is a holiday.

I have tried to use the WORKDAY formula, but this does not appear to include weekends?
This is the formula I have been using to test it:
=WORKDAY(A2,7,G3:G9) Where A2 is the start date and G3:G9 are the holidays… There are lots more holidays than this but I have only put in a few as a test. This seems to exclude the hoildays give the wrong dates as it does not include weekends?

I am a bit of a novice with excel/formulas so any help would be much appreciated. Is there an easy way to do this in excel?

Thanks