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.

## Download Practice Workbook

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

## Steps to Calculate Hours Between Two Dates and Times in Excel Excluding Weekends

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 Hours Between Two Times in Excel (6 Methods)**

### 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")`

**Read More:** **How to Calculate Total Hours Worked in a Week in Excel (Top 5 Methods)**

**Similar Readings**

**How to Calculate Hours and Minutes in Excel (7 Handy Ways)****Excel Formula To Calculate Time Worked****How to Calculate Cycle Time in Excel (7 Examples)****Calculate Elapsed Time Between Two Dates in Excel (5 Methods)****How to Calculate Turnaround Time in Excel Excluding Weekends (3 Ways)**

### 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, 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 (3 Methods)**

## 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. You can visit our website **ExcelDemy** to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.