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:

=NETWORKDAYS(StartDate,EndDate,[Holidays])

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:

=NETWORKDAYS.INTL(StartDate,EndDate,[Weekend],[Holidays])

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 

  

Happy calculating!  

  

Nicholas Jankovic

T7 Training Systems

  

Thursday, 11th October 2012

 

HomeInformation and pricingCoursesMake a bookingBlogContact us
© 2011 T7 Training Systems

Magicdust Responsive Website Design

HomeInformation and pricingCoursesMake a bookingBlogContact us