In a lot of companies, employees are paid by their respective working hours. To calculate net work hours in a date range is not an easy task. In this tutorial, you will learn to apply a formula to calculate hours between two dates and times in Excel excluding weekends.

**Table of Contents**Expand

## How to Calculate Hours Between Two Dates and Times in Excel Excluding Weekends: Step-by-Step Procedures

Let’s first introduce our dataset. There are 3 columns: Start Time, End Time, and Work Hours Excluding Weekends. In order to calculate the work hours excluding weekends, just follow the steps below.

### Step 1: Format Start and End Time Cells

- First, select two cells
**B5**and**C5,**then go to the**Home**tab >>**Number**>> click on the**Format Cells**icon as shown in the image below. A**Format Cells**window will pop up.

- Now, under the
**Number**section, select**Custom**from the category list. Then, choose**m/d/yyyy h:mm**from the type list or you can type it into the box. Finally, click**OK.**

**Read More:** Calculate Elapsed Time Between Two Dates in Excel

### Step 2: Enter Formula to Calculate Hours Excluding Weekends

- Now, write down the two dates and times in the respective start and end date-time boxes
**(B5**and**C5).**And then, select cell**D5,**copy the following formula, and paste it into the formula box. After pressing**ENTER,**you will see the number string.

`=(NETWORKDAYS(B5,C5)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),"17:30","8:30")`

### Step 3: Change Output Cell Format to Get the Result in Total Hours

- Now, we have to change the cell’s format to see the actual work hours. For that, select the cell
**D5**>>**Right-click**>> choose**Format Cells.**A**Format Cells**window will appear. - Under the
**Number**section, choose**Custom**from the**Category**list. Then, type**[h]:mm**in the**Type**box. Finally, click**OK.**

Here are the working hours between two dates and times excluding weekends.

🔎 **Formula Breakdown**

**MOD(B5,1)**The

**MOD function**returns a remainder after B5 is divided by 1.

**Output:**0**NETWORKDAYS(B5,B5)**The

**NETWORKDAYS function**returns the number of whole workdays between B5 and B5.

**Output:**0

**MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),”17:30″,”8:30″)**The

**MEDIAN function**returns the median, or the number in the middle of the set of NETWORKDAYS(B5,B5)*MOD(B5,1),”17:30″,”8:30″

**Output:**“17:30”

**MEDIAN(MOD(C5,1),”17:30″,”8:30″)**

**Output:**“17:30”

**NETWORKDAYS(C5,C5)**

**Output:**0

**IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),”17:30″,”8:30″),”17:30″)**The

**IF function**checks whether a condition is met, and returns one value if**TRUE**, and another value if**FALSE.**Therefore, IF(0,”17:30”,”17:30”)

**Output:**“17:30” (As 0 denotes FALSE, and 1 denotes TRUE in Excel logical test)

**“17:30”-“8:30”**

**Output:**0.375

**NETWORKDAYS(B5,C5)-1**

**Output:**1/18/1900 0:00

**(NETWORKDAYS(B5,C5)-1)*(“17:30”-“8:30”)**

**Output:**1/6/1900 18:00

**(NETWORKDAYS(B5,C5)-1)*(“17:30”-“8:30″)+IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),”17:30″,”8:30″),”17:30″)-MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),”17:30″,”8:30”)**

**Output:**7.104166667

**Read More:** Excel Calculate Hours Between Two Times After Midnight

**Download Practice Workbook**

You can download the following practice workbook that we have used to prepare this article.

## Conclusion

In this tutorial, I have discussed a formula to calculate hours between two dates and times in Excel excluding weekends. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

- How to Calculate Hours Between Two Times in Excel
- How to Calculate Hours and Minutes in Excel
- How to Calculate Hours from Date and Time in Excel
- How to Calculate Time Difference Between AM and PM in Excel
- How to Calculate Difference Between Two Dates and Times in Excel
- How to Calculate Travel Time Between Two Cities in Excel

**<< Go Back to Calculate Hours | Calculate Time | Date-Time in Excel | Learn Excel**