Shajratul Alam Towhid
If you are looking for man hours calculation in Excel, then you are in the right place. In every working sector, the company or the organization needs to calculate the total working hours of the employees for the need to pay them and make a record. In this article, we’ll try to discuss man hours calculation in Excel.
Download Practice Workbook
6 Ways of Man Hours Calculation in Excel
A man working in a sector can work at different hours and the ways to calculate them are different. Excel offers all of these different ways to calculate man hours. All of those are very easy to use.
1. Using Subtraction
If we know the starting time and the finishing time, we can calculate hours by using subtraction.
1.1. Time Value Difference Less than 24 Hours
Suppose, we have the following dataset with column headers as Start Time, Finish Time. We need to calculate Total Hours in Column D. Here, we can see that the time value difference is less than 24 hours.
In this case, we can apply the simple subtraction formula in the D5 cell like this.
=C5-B5
Here, C5 and B5 refer to the first Finish Time and Start Time respectively.
Secondly, press ENTER to get the output as 6:00 hours. That means 6 hours 0 minutes.
Thirdly, use the Fill Handle by dragging down the cursor while holding the right-bottom corner of the D5 cell like this.
Eventually, we’ll get the Total Hours as output like this.
1.2. Time Value Difference More than 24 Hours
In the following dataset, the time difference between the Start Time and Finish Time is more than 24 hours. In this case, we can’t apply the subtraction method directly. Suppose, we want to find Total Hours in Column D.
Firstly, write the following formula in the D5 cell like this.
=(C5-B5)*24
Secondly, press ENTER.
Thirdly, use the Fill Handle to get all the outputs.
Read More: How to Calculate Time Difference in Excel (13 Ways)
2. Calculating Working Hours Between Two Consecutive Days
Suppose, we have the following dataset where we need to calculate hours between two consecutive days. We need to get output in the D Column. In this case, we need to use the IF function.
Firstly, write the formula in the D5 cell.
=IF(B5>C5,C5+1,C5)-B5Here, B5 and C5 refer to the first Start Time and Finish Time respectively.
Similarly, press ENTER and use the Fill Handle.
Eventually, the output will be like this.
Read More: How to Calculate Total Hours Worked in a Week in Excel (Top 5 Methods)
3. Applying MOD Function for Negative Hour Difference
While using subtraction, we always do Finish Time – Start Time. When we get the negative output by using this subtraction method we need to use the MOD function. This MOD function actually ignores the negative value of the output and thus gives a positive output.
Here, we have the following dataset and need to calculate Total Hours in Column D.
Firstly, write the formula in the D5 cell like this.
=MOD(C5-B5,1)
Secondly, press ENTER and use the Fill Handle and the output will be like this.
Eventually, we can see that all the outputs here are positive. If we didn’t use the MOD function, in this case, the output would be negative.
Read More: How to Subtract and Display Negative Time in Excel (3 Methods)
Similar Readings
- How to Calculate Median Follow-up Time in Excel (with Easy Steps)
- Calculate Billable Hours in Excel (4 Effective Ways)
- How to Create an Injection Molding Cycle Time Calculator in Excel
- Calculate Lag Time in Excel (2 Suitable Methods)
- How to Calculate Travel Time Between Two Cities in Excel
4. Using Text Function to Calculate Working Hours
We can simply use the TEXT function to calculate hours. Here, we have to find the hours in Column D of the following dataset.
Eventually, write the formula in the D5 cell.
=TEXT(C5-B5,"[hh]:mm")
Here, hh and mm refer to Hours and Minutes respectively. [hh]:mm refer that we’ll get the output in this format i.e. first hour then a minute.
If we press ENTER and use the Fill Handle, the output will be like this.
Read More: Excel Formula To Calculate Time Worked
5. Utilizing HOUR Function to Get Working Hours
We can use the HOUR function also to calculate hours. But in this case, the output will have only hours in integer format. We won’t get any decimal value or minute value.
Here, in the following dataset, we need to calculate Total Hours in Column D.
Initially, write the following formula in the D5 cell like this.
=HOUR(C5-B5)
Secondly, press ENTER.
Lastly, use the Fill Handle.
Consequently, the output is like this.
Read More: How to Calculate Hours Worked and Overtime Using Excel Formula
6. Using NOW Function to Calculate Hours from Now
If we want to calculate hours from the current time, we need to use the NOW function.
Suppose, in the following dataset, we need to calculate Hours Worked by Now in Column D.
Write the formula in the D5 cell like this.
=NOW()-B5
Here, B5 refers to the first Start Time which is 11:00 AM.
Similarly, as before, press ENTER and use the Fill Handle.
Eventually, we’ll get the Hours Worked by Now as output.
Read More: How to Calculate Hours Worked Minus Lunch with Excel Formula
How to Calculate Total Working Hours in Excel
By using Excel we can calculate the total working hours of an employee or all the employees in a week, month, year, or according to our requirements.
Here, we have the following dataset with column headers as Days and Completed Hours. We have included the seven days of a week and want to calculate the total Completed Hours of an employee in a week in the C12 cell. In this case, we need to use the SUM function.
Firstly, write the formula in the C12 cell like this.
=SUM(C5:C11)
Press ENTER.
Eventually, we’ll get the output as 59.32 hours.
Read More: How to Calculate Working Days in Excel Excluding Weekends and Holidays
Things to Remember
- While using the HOUR function, we will get only hours in integer format. We won’t get any decimal value or minute value.
- To get the desired output in a specific format we need to fix the format in the Number box of the Home
Conclusion
That’s all about today’s session. And these are the ways to calculate man hours in Excel. We strongly believe this article would be highly beneficial for you. Don’t forget to share your thoughts and queries in the comments section and explore our website ExcelDemy, a one-stop Excel solution provider.
Related Articles
- How to Calculate Time Weighted Return in Excel (2 Simple Ways)
- How to Calculate Lead Time in Excel (2 Suitable Examples)
- Calculate Hours Between Two Dates and Times in Excel Excluding Weekends
- How to Calculate On Time Delivery Performance in Excel
- How to Calculate Slack Time in Excel (2 Ideal Examples)
- Excel Calculate Hours between Two Times after Midnight (3 Methods)
