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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

Hi there. I am Hafiz, graduated from BUET. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. Now you can see my articles in the ExcelDemy blog.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  