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.


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.

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


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

Hafiz Islam
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo