Calculating working days between dates in Excel
At times, you may need to calculate the amount of working days between two dates. While this may seem simple at first glance with = ( (End date - Start date) / 7 ) * 5 problems can arise when the start date is not the same day of the week as the end date - meaning the working days are close to, but not exactly, five sevenths of the total number of days... and what about public holidays? Fortunately, Excel comes to the rescue with the NETWORKDAYS function.
Looking for Microsoft Excel training? Click here for our courses.
The NETWORKDAYS function
NETWORKDAYS works as follows:
Naturally, the dates must be in correct date format - for example 5/3/2014.
The square brackets around Holidays indicates that this part is optional.
View the graphic below to see this function in action:
The NETWORKDAYS.INTL function
In the event that you have a different amount of working days in the week instead of the standard five (for example, six working days a week), or the non-working days are different to Saturday and Sunday (for example, Thursday and Friday) and you are using Excel 2010 or later, then you can use the NETWORKDAYS.INTL function which works as follows:
The [Weekend] parameter requires one of the numbers below:
To indicate a two-day weekend, enter...
1 for Saturday and Sunday
2 for Sunday and Monday
3 for Monday and Tuesday
4 for Tuesday and Wednesday
5 for Wednesday and Thursday
6 for Thursday and Friday
7 for Friday and Saturday
To indicate a one-day weekend, enter...
11 for Sunday
12 for Monday
13 for Tuesday
14 for Wednesday
15 for Thursday
16 for Friday
17 for Saturday
T7 Training Systems
Thursday, 11th October 2012