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.
Man Hours Calculation in Excel: 6 Ways
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 the 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.
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.
Secondly, press ENTER.
Thirdly, use the Fill Handle to get all the outputs.
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.
Here, 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.
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.
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.
4. Using Text Function to Calculate Working Hours
We can 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.
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.
5. Utilizing the 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.
Secondly, press ENTER.
Lastly, use the Fill Handle.
Consequently, the output is like this.
6. Using the 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.
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 Billable Hours in Excel
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.
Eventually, we’ll get the output as 59.32 hours.
Read More: Excel Formula for Overtime over 8 Hours
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 tab.
Download Practice Workbook
That’s all about today’s session. 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, a one-stop Excel solution provider.
- How to Calculate Hours Worked and Overtime Using Excel Formula
- Excel Formula to Calculate Overtime and Double Time
- Excel Formula To Calculate Time Worked
- How to Create an Injection Molding Cycle Time Calculator in Excel