Today is May 1st 2013 (I know it’s not but for the purposes of this demo it is). The new system goes live on 1st October and user training has to be completed before this date.
Training can only take place on Monday – Friday and in addition, there are several dates that training courses definitely can’t be run – there’s a national holiday, a “company leave day” where the company is shut, a “company shutdown week” and a team-building day for the training team. These dates are listed in B7:B14 of the spreadsheet below.
So the question is…how many days are available to deliver the training?
Once we have that information, along with the number of people that require training, we can work out how many courses we need to schedule per week.
To calculate the number of available days, use the NETWORKDAYS function. This calculates the difference between two dates but excludes weekends and holidays.
Weekends are automatically excluded as Excel knows what dates are weekends but it doesn’t know which dates are holidays (it doesn’t even know 25th December is Christmas Day) so you have to list these down a column (in this case, B7:B14)
The formula in B17 is =NETWORKDAYS(B3,B4,B7:B14)
B3 is the starting date
B4 is the ending date
B7:B14 contains the dates to be treated as holidays
The holidays argument is actually optional, so if training could be delivered Monday – Friday between 1st May and 1st October, you would use the formula