Hafiz Islam
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.
Related Articles
- Subtract Hours from Time in Excel (2 Easy Ways)
- How to Subtract Date and Time in Excel (6 Easy Ways)
- Calculate Total Hours in Excel (9 Easy Methods)
- How to Calculate Time in Excel (16 Possible Ways)
- Calculate Difference Between Two Dates and Times in Excel
- How to Calculate Hours from Date and Time in Excel
