Man Hours Calculation in Excel (6 Useful Methods)

Method 1 – Using Subtraction

If we know the starting time and the finishing time, we can calculate hours by using subtraction.

Case 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.

• Apply the simple subtraction formula in the D5:

• Press Enter to get the output.
• Use the Fill Handle by dragging down the cursor while holding the right-bottom corner of the D5 cell.

• Eventually, we’ll get the Total Hours as output like this.

Case 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.

• Write the following formula in the D5 cell:

• Press Enter.
• Use the Fill Handle to get all the outputs.

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

Method 2 – Calculating Working Hours Between Two Consecutive Days

Suppose we have the following dataset where we need to calculate hours between two consecutive days.

• Write this formula in the D5 cell.

• Press Enter and use the Fill Handle to copy the formula across column D.
• Eventually, the output will be like this.

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

Method 3 – Applying MOD Function for Negative Hour Difference

Here, we have the following dataset and need to calculate Total Hours in Column D.

• Write the following formula in the D5 cell:

• Press Enter and use the Fill Handle to copy the formula to the other cells.
• We can see that all the outputs here are positive.

Read More: How to Calculate On Time Delivery Performance in Excel

Method 4 – Using Text Function to Calculate Working Hours

Let’s find the hours in Column D of the following dataset.

• 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.

• Press Enter.
• Use the Fill Handle to get all the results in the column.

Read More: How to Calculate Production per Hour in Excel

Method 5 – Utilizing the HOUR Function to Get Working Hours

In the following dataset, we need to calculate Total Hours in Column D.

• Write the following formula in D5:

• Press ENTER.
• Use the Fill Handle to copy the formula across column D.

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

Method 6 – Using the NOW Function to Calculate Hours from Now

Suppose we need to calculate Hours Worked by Now in Column D.

• Write this formula in the D5 cell:

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

• Press Enter and use the Fill Handle.

Read More: How to Calculate Billable Hours in Excel

How to Calculate Total Working Hours in Excel?

Consider 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.

• Copy this formula in the C12 cell:

• Press Enter.

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

Related Articles

• 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

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