Calculate Hours Between Two Dates and Times in Excel Excluding Weekends

When it comes to giving the daily payment for any job or work, we need to calculate the working hours. There are many companies that pay their workers on a working-hour basis. In this article, we are going to show you how to calculate hours between two dates and times excluding weekends in Excel. To calculate hours between two dates and times excluding weekends in Excel, we have to use several functions including the NETWORKDAYS, IF, MEDIAN, and MOD functions. Initially, it may seem difficult to you but gradually you will understand the whole procedure as well.

Let, James was an employee of a renowned industry. He joined the industry in 2013 and left the job in 2017. We will calculate his working hours between these two dates and times excluding weekends in Excel. So. let’s get started.

Calculate Hours Between Two Dates and Times in Excel Excluding Weekends


πŸ“‚Β  Download Excel File

Download free Excel workbook from here:


Learn to Calculate Hours Between Two Dates and Times Excluding Weekends in Excel

While writing this article, β€œCalculate Hours Between Two Dates and Times Excluding Weekends in Excel”, we’ve considered a dataset that contains approximately 5 columns and 16 rows. In this dataset, we added Name, Entering Date, Leaving Date, and Working Hours (Excluding Weekends) to make you better understand. With the help of these data, we perform our required operation. But if you want, you can change the entities and put your own values.

Sample dataset containing a register of employee

To calculate hours between two dates and times excluding weekends in Excel, we will utilize the Excel functions here. The NETWORKDAYS, IF, MEDIAN, and MOD functions will give the output, and then we will change the output to a custom format. You need to be very careful while performing this operation because the formula contains several functions. You can follow the given instructions to achieve the approach.

Basically, there are two steps to calculate hours between two dates and times excluding weekends. We are going to discuss every step elaborately.

STEP

Step 1: Create Formula to Calculate Hours

In this step, you need to do the following things.

  • Initially, you need to go to the ExcelΒ worksheet.
  • Secondly, select cell E6.
  • Thirdly, write the following formula on that cell

=(NETWORKDAYS(C6,D6)-1)*(β€œ17:30”-β€œ8:20β€³)+IF(NETWORKDAYS(D6,D6),MEDIAN(MOD(D6,1),”17:30β€³,”8:20β€³),”17:30β€³)-MEDIAN(NETWORKDAYS(C6,C6)*MOD(C6,1),”17:30β€³,”8:20”)

πŸ”¨ Formula Breakdown

Here:

πŸ‘‰Β  MOD(C6,1) gives a remainder for C6 following a division by 1.

Result: 0

πŸ‘‰Β  NETWORKDAYS(C6,C6) refers The quantity of complete working days among C6 and C6.

Result: 0

πŸ‘‰Β  MEDIAN(NETWORKDAYS(C6,C6)*MOD(C6,1),”17:30β€³,”8:20β€³) refers that The average or the value in the midpoint of the range of values returned by the MEDIAN function is NETWORKDAYS(C6,C6)*MOD(C6,1),”17:30,”,”8:20β€³.

Result: 17:30

πŸ‘‰Β  MEDIAN(MOD(D6,1),”17:30β€³,”8:20β€³) gives

Result: 17:30

πŸ‘‰Β  NETWORKDAYS(D6,D6) provides

Result: 0

πŸ‘‰Β  NETWORKDAYS(C6,D6)-1 gives

Result: 07/02/1903 0:00

πŸ‘‰Β  (NETWORKDAYS(C6,D6)-1)*(β€œ17:30”-β€œ8:20”) provides

Result: 08/03/1901 3:00

πŸ‘‰Β  IF(NETWORKDAYS(D6,D6),MEDIAN(MOD(D6,1),”17:30β€³,”8:20β€³),”17:30β€³) refers that the IF function determines whether a criteria is satisfied and outputs two values: one if TRUE as well as another if FALSE, IF(0,”17:30β€³,”17:30β€³) follows.

Result: β€œ17:30” (In an Excel analytical test, 0 indicates FALSE and 1 indicates TRUE.)

πŸ‘‰Β  (NETWORKDAYS(C6,D6)-1)*(β€œ17:30”-β€œ8:20”) + IF(NETWORKDAYS(D6,D6), MEDIAN(MOD(D6,1), β€œ17:30β€³,”8:20β€³),”17:30β€³)-MEDIAN(NETWORKDAYS(C6,C6)*MOD(C6,1),”17:30β€³,”8:20”) provides

Result: 403.5069444

  • Fourthly, press the EnterΒ key.
  • This format is General So, we need to use the Custom format to get the appropriate output as well.

Using the NETWORKDAYS, IF, MEDIAN and MOD functions to calculate hours

πŸ“• Read More: How to Calculate Turnaround Time in Excel Excluding Weekends

STEP

Step 2: Apply Custom Format to Get Hour, Minute and Second Value

For the desired output, you need to maintain this step properly.

  • Select cell E6.
  • Secondly, go to the HomeΒ tab.
  • Thirdly, choose the Number feature and belatedly, click on the Number Format.

Applying the Number Format from the Data tab

  • Now you will get a dialogue box name Format Cells.
  • Thirdly, click on the Custom option and choose the Type:

[h]:mm:ss

  • Afterward, tap on the OKΒ button.

Choosing the Custom format from the Format Cells

  • Finally, you will find the desired output that you wanted to have.

Output shown in Hour:Minute:Second Format in Excel

Undoubtedly, this approach may look slightly difficult but we hope you can do this easily if you follow the above instructions properly.

πŸ“• Read More: 9 Tricks to Calculate Time Difference Between AM and PM in Excel


πŸ“„ Important Notes

You should be aware of the following things while performing the processes mentioned above:

πŸ–ŠοΈΒ  Initially, you need to be careful while using the Excel functions.

πŸ–ŠοΈΒ  You should be conscious when you intend to use the Custom format as well.


πŸ“ Takeaways from This Article

If we summarize the whole article, we have got some points.

πŸ“ŒΒ  Firstly, we used the NETWORKDAYS, IF, MEDIAN, and MOD functions.

πŸ“ŒΒ  Secondly, we applied the Custom format to calculate hours between two dates and times excluding weekends.


Conclusion

We wish that you would be able to calculate hours between two dates and times excluding weekends in Excel using this article. You can easily follow the given approach without facing any problems we hope. If you have any skepticism, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.


Related Articles

(Visited 39 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo