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.


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.

Calculate Hours Between Two Dates and Times in Excel Excluding Weekends

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

Calculating Hours Between Two Dates and Times in Excel Excluding Weekends

Read More: Calculate Elapsed Time Between Two Dates in Excel


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.

Calculating Hours Between Two Dates and Times in Excel Excluding Weekends

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

Read More: Excel Calculate Hours Between Two Times After Midnight


Download Practice Workbook

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


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.


Related Articles


<< Go Back to Calculate Hours | Calculate Time | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hafizul Islam
Hafizul Islam

Hafizul Islam is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo