Finding the number of work hours between two dates can be a little more challenging due to the fact that you must also consider holidays and weekends. After finding the number of business days, you can easily multiply this number by daily work hours (i.e. 8 hours) to find the final total. In this guide, we are going to show you how to calculate work hours between two dates in Excel, and create your own business day calculator.
Creating a Business Day Calculator
Excel has two functions that will be used in our business day calculator:
The NETWORKDAYS.INTL is a newer version of the NETWORKDAYS, and functions are currently still in use. Let's briefly see what each can do.
NETWORKDAYS
The NETWORKDAYS function calculates the number of work days between two dates. The function excludes weekends by default, and can also be set in a way to exclude a number of holidays. The difference between the NETWORKDAYS and NETWORKDAYS.INTL is how they handle weekends. The NETWORKDAYS function assumes that Saturday and Sunday are the weekends by default.
Syntax
The NETWORKDAYS function takes 3 arguments:
| start_date | Start date of the period you want to get working days between. |
| end_date | End date of the period you want to get working days between. |
| [holidays] | Optional. A range of date values to be excluded aside from weekends. An array constant can be used as well. |
Since the [holidays] argument is optional, you can skip it if you don't want to exclude any other dates besides weekends.
The following demonstrate how to use the NETWORKDAYS function to calculate the work hours between two dates. Please note that formulas here contain named ranges to make it easier to read the formulas, this is not required.
With holidays: =NETWORKDAYS(start.2,end.2,holidays) * hours
Please see Function: NETWORKDAYS for more examples.
NETWORKDAYS.INTL
The NETWORKDAYS.INTL function became available with Excel 2010. This function allows specifying which days of the week are weekends, and can also include a list of holidays. The function contains an additional argument [weekend].
The [weekend] argument can take specific number codes or a string value to identify which days of the week are holidays. If you set this to 1, or omit the argument, the function assumes that Saturday and Sunday are weekends, similar to the NETWORKDAYS function. Here is the list of codes:
| Weekend number | Weekend days (Non Business Days) |
| 1 or omitted | Saturday, Sunday |
| 2 | Sunday, Monday |
| 3 | Monday, Tuesday |
| 4 | Tuesday, Wednesday |
| 5 | Wednesday, Thursday |
| 6 | Thursday, Friday |
| 7 | Friday, Saturday |
| 11 | Sunday only |
| 12 | Monday only |
| 13 | Tuesday only |
| 14 | Wednesday only |
| 15 | Thursday only |
| 16 | Friday only |
| 17 | Saturday only |
Alternatively, you can enter a 7-character string that indicates the business days and weekends. The days start with Monday and you can use zeros and ones (0, 1) which represent a business day and a no-business day respectively. For example, string "1000001" indicates that Mondays and Sundays are non-workdays.
Syntax
Let's look at the syntax:
| start_date | Start date of the period you want to get business days between. |
| end_date | End date of the period you want to get business days between. |
| [weekend] | Optional. A number or string value that indicates which days of the week are the weekends. Please see the table below for available options. |
| [holidays] | Optional. A range of date values to be excluded aside from the weekends. You can also use an array of constants. |
Let's see how you can use the NETWORKDAYS.INTL function to calculate work hours between two dates and create your own business day calculator.
The example in the screenshot below uses the value 11 (named range weekend.3) for the [weekend] argument. 11 means that only Sunday is a weekend. As a result, working day and working hour numbers are higher than the previous example, which assumed two day weekends.
Please see Function: NETWORKDAYS.INTL for more examples.
