The NETWORKDAYS.INTL function was added in Excel 2010. It allows to calculate how may work days between two dates using non-standard weekends. Some countries don’t have Saturday/Sunday weekends.
That works great for weekends (non-working days) that are consecutive.
Lets say you have a part time workforce and each person has different days off. How can you calculate how many work days per person? The answer is at the end of the post, as is a link to an example file.
The added option in the NETWORKDAYS.INTL function allows you to specify a customised working week using ones and zeroes. Hat tip to Mr Excel (Bill Jellen) for including this in one of his books.
Syntax
NETWORKDAYS.INTL(Start_date, End_Date,Weekend,Holidays)
Start_date and End_date are self explanatory. Holidays is a range that specifies the public holiday dates for the region involved.
Weekend is a number that specifies what the weekend is. This number handles two and one day weekends – see image below.
The trick is that the third argument Weekend, can also take a string of 7 numbers.
The first digit represents Monday and the last digit being Sunday. A 1 means it is a day off and a zero means it is a workday.
This allows you to specify a customised working week. See example below.
The question I posed at the start of the post was how do you handle staff who have different days off.
A lookup table will allow you to specify a name and a weekday sequence.
A VLOOKUP function can then extract the relevant weekday sequence to use in the NETWORKDAYS.INTL.
The example below demonstrates the technique.
Note: you need to use an apostrophe if there are leading zeros, as per cell E6. See below.
Example file link below.
Please note: I reserve the right to delete comments that are offensive or off-topic.