How to Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)

Why Formatting Is Important For Measuring Time in Excel?

Excel stores Date and Time as numbers. An integer represents a complete day with a starting daytime (i.e., 12:00AM) and the decimal part of the number represents a specific section of a day (i.e., Hour, Minute, and Second).

When calculating time differences, if you subtract times without formatting the cells you’ll get the wrong result as shown below.

To avoid this problem, pre-format the cells where you want to show the results.

➤ Right-click on the value (i.e.,0372) and select Format Cells. The Format Cells window will open. Choose Time as the Number Format and 13:30 as the Type. Click OK.

Press CTRL+1 to bring up the Format Cells window.

➤ You can also select Custom as Number Format and h:mm as Type.

Excel will now display the correct time difference.

Method 1 – Applying Subtraction to Calculate Hours and Minutes for Payroll Excel

Pre-format the cells as shown in the Why Formatting is Important… section.

Step 1: Enter the following formula in any adjacent cell (i.e., E7).

D7 and C7 are cell references. Subtraction sign (–) between them, results in the worked time on a specific date.

Step 2: Press ENTER and Drag the Fill Handle down to the rest of the cells.

To calculate the payroll for any individual days,

Step 3: Enter the following formula in any adjacent cell (i.e., F7).

C4 is the cell reference for Per Hour Pay, E7 for worked time, and we multiply 24 as Excel stores time (i.e., E7) in day formats when it executes any operations.

$C$4*E7*24 becomes the Per Day Pay for the employee.

 Step 4: Press ENTER and Drag the Fill Handle down to the rest of the cells.

Read More: Excel Formula for Overtime over 8 Hours

Method 2 – Using TEXT Function to Calculate Hours and Minutes for Payroll Excel

The TEXT function transforms a return value in a specific format. We can use the TEXT function to calculate the worked hours and minutes from given times. The syntax of the TEXT function is:

Text(value, format_text)

 In the syntax,

value; is the value you want to format.

format_text; is the format you want the result into.

Step 1: Enter the following formula in any blank cell (i.e., E7).

In the formula,

D7-C7= value

“h:mm”=format_text

 Step 2: Press ENTER and Drag the Fill Handle down to the rest of the cells.

Step 3: Repeat Steps 3 and 4 of Method 1 with this formula to get the Total Pay Amount in the cells as shown in the following image.

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

Method 3 – Using HOUR and MINUTE Function

We can calculate the hours and minutes separately using the HOUR and MINUTE functions. The syntax of both functions are,

HOUR(serial_number)

MINUTE(serial_number)

In the syntax,

serial_number; is the value that contains the hours or minutes you want to find.

Step 1: Add the following formula for hours in any adjacent cell (i.e., E7).

Step 2: Press ENTER and Drag the Fill Handle down to the rest of the cells.

Step 3: Repeat Steps 1 and 2 replacing the HOUR formula with the MINUTE formula below,

Step 4: To count the total pay, enter the following formula in any cell (i.e., G7).

F7/60 turns minutes into hours and adding this with E7, we get total worked hours. Multiplying the total worked hours with Per Hour Pay gives the result for Total Pay.

Step 5: Press ENTER and Drag the Fill Handle down to the rest of the cells.

Method 4 – Using the TIME Function to Calculate Hours and Minutes for Payroll Excel

The TIME function takes three arguments and adds or subtracts them individually. We’ll subtract the hours and minutes to get the worked time in our dataset. The syntax of the TIME function is,

TIME(hour, minute, second)

Step 1: Paste the below formula in any cell (i.e., E7),

Step 2: Press ENTER and Drag the Fill Handle down to the rest of the cells.

Step 3: Repeat Steps 3 and 4 of Method 1 with this formula. You’ll get all the Total Pay amount.

Read More: How to Calculate Hours Worked and Overtime Using Excel Formula

Method 5 – Using MOD Function

The MOD function fetches the hours and minutes with the help of subtraction. The syntax of the MOD function is,

 MOD(number, divisor)

number; the value of which you want to get the remainder.

divisor; the number by which you want to divide the number.

We’ll use the subtraction value as number and 1 as divisor to count the hours and minutes.

Step 1: Enter the formula in cell E7.

Step 2: Press ENTER and Drag the Fill Handle down to the rest of the cells.

Step 3: Repeat Steps 3 and 4 of Method 1 with this formula.

Read More: Excel Formula to Calculate Overtime and Double Time

Method 6 – Using the IF Function to Calculate Hours and Minutes for Payroll Excel

We can use the IF function to display hours and minutes separately in one cell. The syntax of the IF function is,

IF (logical_test, [value_if_true], [value_if_false])

In the syntax, the IF function performs a logical_test, and depending on the test result TRUE or FALSE it displays pre-wrote text [value_if_true] or [value_if_false].

Step 1: Paste the following formula in cell E6.

In the formula, HOUR(D6-C6)>0 or MINUTE(D6-C6)>0 works as logical_test. HOUR(D6-C6) & “hours “ or MINUTE(D6-C6) & ” minutes” will be displayed if the test result is TRUE and “” will be displayed if the test result is FALSE.

Step 2: Press ENTER and Drag the Fill Handle down to the rest of the cells.

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

Method 7 – Calculating Elapsed Time

The NOW function can be used to calculate the hours and minutes from a given time to current time. The syntax of the NOW function is,

NOW()

The NOW function returns the current day and time.

Step 1: Paste the following formula in cell D6.

NOW subtracts the hours and minutes from a given time (i.e., C6).

Step 2: Press ENTER and Drag the Fill Handle down to the rest of the cells.

Download Excel Workbook

Related Articles

• How to Calculate Production per Hour in Excel
• How to Calculate Total Hours Worked in a Week in Excel
• How to Calculate Billable Hours in Excel
• Excel Formula To Calculate Time Worked
• Man Hours Calculation in Excel
• How to Create an Injection Molding Cycle Time Calculator in Excel

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