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.


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.

man hours calculation in excel

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.

Using Subtraction

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.

man hours calculation in excel


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.

Using Subtraction

Firstly, write the following formula in the D5 cell like this.

=(C5-B5)*24

man hours calculation in excel

Secondly, press ENTER.
Thirdly, use the Fill Handle to get all the outputs.

Read More: How to Calculate Hours Worked Minus Lunch with Excel Formula


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.

Calculating Working Hours Between Two Consecutive Days

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.

man hours calculation in excel

Similarly, press ENTER and use the Fill Handle.
Eventually, the output will be like this.

Read More: How to Calculate Hours and Minutes for Payroll Excel


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.

Applying MOD Function for Negative Hour Difference

Firstly, write the formula in the D5 cell like this.

=MOD(C5-B5,1)

man hours calculation in excel

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 Calculate On Time Delivery Performance in Excel


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.

Using Text Function to Calculate Working Hours

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.

man hours calculation in excel

If we press ENTER and use the Fill Handle, the output will be like this.

Read More: How to Calculate Production per Hour in Excel


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.

Utilizing HOUR Function to Get Working Hours

Initially, write the following formula in the D5 cell like this.

=HOUR(C5-B5)

man hours calculation in excel

Secondly, press ENTER.
Lastly, use the Fill Handle.
Consequently, the output is like this.

Read More: How to Calculate Total Hours Worked in a Week in Excel


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.

Using NOW Function to Calculate Hours from Now

Write the formula in the D5 cell like this.

=NOW()-B5

Here, B5 refers to the first Start Time which is 11:00 AM.

man hours calculation in excel

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.

How to Calculate Total Working Hours in Excel

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: 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


Conclusion

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.


Related Articles


<< Go Back to Calculate Hours | Calculate Time | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shajratul Alam Towhid
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo