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

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.

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. ### 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, 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

## 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.

## Related Articles #### Hafiz Islam

We will be happy to hear your thoughts 