# Man Hours Calculation in Excel (6 Useful Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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. ### 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)-B5`

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.

`=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. ### 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. ### 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. ## 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 Shajratul Alam Towhid

Hello, myself Shajratul Alam Towhid. Basically, I am a Naval Architect who wants to expand knowledge in the field of Microsoft Excel. I wish all of my articles will be beneficial for the readers.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  