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.