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.
๐ย 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.
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 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.
๐ Read More: How to Calculate Turnaround Time in Excel Excluding Weekends
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.
- 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.
- Finally, you will find the desired output that you wanted to have.
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
- 4 ways to Create Excel Formula for Overtime Over 8 Hours
- 7 Examples to Calculate Duration of Time in Excel
- How to Calculate Overtime and Double Time with Formula in Excel
- 5 Easy Ways to Fix When SUM Time Is Not Working in Excel
- 4 Easy Methods to Calculate Production per Hour in Excel
- 5 Ways to Calculate Total Hours Worked in a Week in Excel
- 4 Examples of Timesheet Formula with Lunch Break in Excel