Watch the video below, follow the tutorial and download the featured file here.
If you need to calculate the number of working days between two dates use the NETWORKDAYS function. The function will make the assumption that you do not work Saturdays and Sundays (if this is the not the case –see NETWORKDAYS.INTL below). You can also exclude holiday dates, such as bank holidays, but you will need to list them somewhere in your workbook – doesn’t matter where. Here’s my holiday list – the descriptions of the holiday are useful but do not perform part of the calculation. Make sure your dates are in proper date format.
The NETWORKDAYs function has three arguments. It needs to know a start date, an end date and holidays (optional) – see below.
Note that the holidays are expressed as a range: E2:E17.
In practical terms the function returns one too many days in its calculations. If you did a calculation on two consecutive working days it would return 2 as the answer – it counts the start date and end date. This is OK if this is your intention, otherwise just suffix the formula with -1, ie =NETWORKDAYS(B7,B8,E2:E17)-1
If you want the formula to calculate the number of working days between today’s date and the end date use the TODAY() function in the start_date argument. This way, the function will always give an up-to-date calculation whenever the workbook is opened.
NETWORKDAYS.INTL Function
NETWORKDAYS.INTL is for you if your work week pattern isn’t Monday to Friday. The function contains four arguments. The third asks for a weekend pattern. As you can see from the screen dump below that you can specify the weekend pattern with a number, so 3 would specify that you do not work Monday and Tuesday.
WORKDAY Function
The WORKDAY function allows you to add working days to a start date to calculate a finish date. It will assume you work Monday to Friday.
Just like the NETWORKDAYS function you have the option to specify holidays in a list, but otherwise you just need to specify a start date and then the number of working days. The arguments order is Start Date, Days, Holidays
WORKDAY.INTL Function
Like NETWORKDAYS.INTL, use WORKDAY.INTL when your weekend doesn’t fall on Saturday and Sunday.
