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.

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.

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

Download Practice Workbook

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

Calculating Hours Excluding Weekends.xlsx

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.