Quick Navigation

- 1Details of the Net Work Hours Formula
- 2Setting Up the Variables for Net Work Hours
- 2.1Setting Up the HolidayList Named Range
- 3The Net Work Hours Formula
- 4Non-Standard Weekends with NETWORKDAYS.INTL
- 524 Hour Net Workday Hours Variation
- 6How the Net Work Hours Formula Works
- 6.1Step 1
- 6.2Step 2
- 6.3Step 3
- 6.4Step 4
- 6.5Step 5
- 7Net Work Hours Example Download

Counting the number of hours between dates and times in Excel is normally a straightforward process. Since Excel stores dates as decimal numbers, you can just subtract the two to get your result. But when you are working with business hours, like for time sheets or hours worked, you need to take weekends and holidays into account. Excel has a function called **NETWORKDAYS**, but this only works with complete days. To calculate the Net Work Hours between two dates, we need to build our own formula. Here’s how…

## Details of the Net Work Hours Formula

A lot of assumptions have to be made about any formula that works with dates and times. Definitions of terms like work day, weekend, and holiday can vary, so this is a summary of the assumptions of this net work hours formula:

- The eligible work hours in a given work day start and end at the same time each work day.
- Weekend days do not count as work days. The weekend is defined as Saturday and Sunday of each week (look ahead to
**Non-Standard Weekends with NETWORKDAYS.INTL**to change this). - Holidays, as listed, do not count as work days.
- Partial work days at the start and end of a date period are included for their fraction of hours.
- If the start time of the work day is later than the end time of the work day, the formula nets zero.
- If the start date and time is later than the end date and time, the formula nets zero.

There are modifications available to the above rules later in this tutorial:

If your definition of weekend is different than above and you have Excel 2007 or newer, you can look ahead to **Non-Standard Weekends with NETWORKDAYS.INTL** to change this.

If you want to include all 24 hours of each work day rather than a specified range of work hours, you can look ahead to **24 Hour Net Workday Hours Variation** to tally the total number of hours each work day.

## Setting Up the Variables for Net Work Hours

In order to calculate the net work hours between two dates, we need to have some information to start with. Here are the variables necessary to make the net work hours formula work.

**$Start_Time** – This is the time that the work day starts, as an Excel-formatted time. (e.g. 9:00AM) No date should be attached. The variable is marked with a dollar sign because you will probably want to anchor this cell reference in the formula.

**$End_Time** – This is the time that the work day ends, as an Excel-formatted time. (e.g. 5:00PM) No date should be attached. The variable is marked with a dollar sign because you will probably want to anchor this cell reference in the formula.

**Start_Date** – This is the starting date and time for your period, as an Excel-formatted date and time (e.g. 12/30/2013 11:00AM)

**End_Date** – This is the ending date and time for your period, as an Excel-formatted date and time (e.g. 1/14/2014 4:00PM)

**HolidayList** – This is a named range containing a list of days that are to be considered holidays and not counted as work days, even if they are not on the weekend. (e.g. New Year’s Day – 1/01/2013) It is usually located elsewhere on the worksheet.

### Setting Up the HolidayList Named Range

The function **NETWORKDAYS** takes an array input called *[holidays]* that lists all the non-weekend days that should be excluded from the calculation. This means that we could give the function a series of dates in brackets, but they would need to be formatted in Excel date format. This means that they need to be converted using a **DATE** function. (See the Definitive Guide to Using Dates and Times in Excel for an explanation.) An easier approach is to build a named range.

In a separate worksheet tab, or in an un-used portion of your current spreadsheet, make a column list of the dates you want to include as holidays in Excel date-format. You can either use a **DATE** function to specify the year, month, and day for each, or let Excel auto-convert as you type.

When you finish your list, select the list (and maybe a few extra cells, if you plan to add to it later). The name of the first cell in the selection appears in the name box above the spreadsheet cells…

With your list selected, select the cell name in the name box, type in “**HolidayList**“, and press **ENTER**.

You have now named the selected cells as an array. It can be referred to in the net work hours formula as **HolidayList** instead of manually entering the range, and the **NETWORKDAYS** function can automatically exclude those dates from calculations.

## The Net Work Hours Formula

Here is the formula, in all it’s glory. Replace the variable names like **$Start_Time** and **HolidayList** with your specific references. You can look to the sample file at the end of this post for an example of the formula in action.

=IF(OR($End_Time<$Start_Time,End_Date<Start_Date),0, (NETWORKDAYS(Start_Date,End_Date,HolidayList) -(NETWORKDAYS(Start_Date,Start_Date,HolidayList) *IF(MOD(Start_Date,1)>$End_Time,1, (MAX($Start_Time,MOD(Start_Date,1))-$Start_Time) /($End_Time-$Start_Time))) -(NETWORKDAYS(End_Date,End_Date,HolidayList) *IF(MOD(End_Date,1)<$Start_Time,1, ($End_Time-MIN($End_Time,MOD(End_Date,1))) /($End_Time-$Start_Time)))) *($End_Time-$Start_Time)*24)

We’ll walk through how it works in a moment, but first, let’s look at a couple of variations that can solve different versions of the net work hours problem.

## Non-Standard Weekends with NETWORKDAYS.INTL

If you have Excel 2007 or newer, you can use a modified version of the **NETWORKDAYS** function called **NETWORKDAYS.INTL** to re-define what days are the “weekend” of each week. **NETWORKDAYS.INTL** takes an additional input called *[weekend]* that defines from a list which days are included in the weekend. The syntax for **NETWORKDAYS** and **NETWORKDAYS.INTL** are as follows:

=NETWORKDAYS(start_date,end_date,[holidays])

=NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])

Note the additional input *[weekend]*. The *[weekend]* input has the following input options:

If you just enter **1**, **NETWORKDAYS.INTL** behaves identically to **NETWORKDAYS**. Other options give you additional flexibility on your definition of “weekend”.

For example, if you want to define the weekend as **Monday** and **Tuesday**, the net work hours formula becomes the following:

=IF(OR($End_Time<$Start_Time,End_Date<Start_Date),0, (NETWORKDAYS.INTL(Start_Date,End_Date,3,HolidayList) -(NETWORKDAYS.INTL(Start_Date,Start_Date,3,HolidayList) *IF(MOD(Start_Date,1)>$End_Time,1, (MAX($Start_Time,MOD(Start_Date,1))-$Start_Time) /($End_Time-$Start_Time))) -(NETWORKDAYS.INTL(End_Date,End_Date,3,HolidayList) *IF(MOD(End_Date,1)<$Start_Time,1, ($End_Time-MIN($End_Time,MOD(End_Date,1))) /($End_Time-$Start_Time)))) *($End_Time-$Start_Time)*24)

Note the change to **NETWORKDAYS.INTL** and the use of **3** as the input for the new *[weekend]* term in the function.

## 24 Hour Net Workday Hours Variation

The above two versions operate on the assumption that there are defined start times and end times to each work day. Sometimes, though, we need to count all 24 hours of each eligible day. This is actually a simpler problem to solve, since we don’t need to look for ineligible hours in eligible work days. Therefore, the formula becomes:

=IF(Start_Date>End_Date,0,(NETWORKDAYS(Start_Date,End_Date,HolidayList) -NETWORKDAYS(Start_Date,Start_Date,HolidayList)*MOD(Start_Date,1) -NETWORKDAYS(End_Date,End_Date,HolidayList)*(1-MOD(End_Date,1)))*24)

If you need to use a non-standard weekend definition, you can replace the **NETWORKDAYS** functions in the formula with the **NETWORKDAYS.INTL** as shown in the Non-Standard Weekend section above.

## How the Net Work Hours Formula Works

There are a lot of moving parts to this formula, so it is useful to step through it procedurally. We’ll use an example case to walk through the logic of the formula step-by-step.

For this example, we’ll assume the following:

**Start_Date** is December 30, 2013 at 11:00 am.

**End_Date** is January 9, 2014 at 12:00 pm.

**$Start_Time** is 9:00 AM.

**$Start_Date** is 5:00 PM.

**HolidayList** includes only January 1, 2014 (New Years Day).

### Step 1

Check if the dates or times are impossible (i.e. the period ends before it starts). If so, return zero, otherwise, continue to step 2.

### Step 2

Add the total number of days in the date series, minus weekends and holidays.

### Step 3

Remove the greater of either the **$Start_Time** or the **Start_Date** time on the **Start_Date** day. Remove the greater of the time after the **$End_Time** or the time after the **End_Date** time on the **End_Date** day. (Not shown: If the **Start_Date** time starts after the **$End_Time**, subtract an entire day. If the **End_Date** time comes before the **$Start_Time**, subtract an entire day.)

### Step 4

For every work day between **Start_Date** and **End_Date**, count only the hours between **$Start_Time** and **$End_Time**.

This is the outcome of the subtractions in the formula.

### Step 5

Add up the partial days in all the periods and multiply by 24 to calculate the total net work hours.

## Net Work Hours Example Download

Here is a set of examples of the three variations of the net work hours formula. You can download the file by clicking the green Excel icon in the bottom right.