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

Often, we need to calculate time differences in Excel. In this article, we demonstrate ways to calculate hours and minutes for payroll in Excel. Multiple functions such as TEXT, HOUR and MINUTE, TIME, MOD, IF, NOW as well as Arithmetic Operator (i.e., Subtraction (-)) can calculate time differences regarding different scenarios.

Dataset-How to Calculate Hours and Minutes for Payroll in Excel

In the dataset, we have a partial month (i.e., December) working schedule of an employee named Ross Johnson. We have to measure the hours and minutes of work done by Ross Johnson.

For the sake of understanding, we demonstrate the calculations with just one employee. You can add as many employees as you want, it just makes the dataset bigger.


Download Excel Workbook


Why Formatting Is Important Measuring Time in Excel?

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

numeric to general date and time

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

general time difference

To avoid this type of occurring, pre-format the cells when you want to show the results.

➤ Right-Click on the value (i.e.,0372), a Menu List comes up. From the Menu List, Select Format Cells. The Format Cells window opens up. Choose Time as Number Format and 13:30 as Type. Then, Click OK.

Format cells

Press CTRL+1 altogether to bring out the Format Cells window.

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

Format Cells-custom

Now, back to the calculation, we’ll get what you suppose to.

general time difference


7 Easy Ways to Calculate Hours and Minutes for Payroll in Excel

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

Subtraction is one of the Arithmetic Operators. It subtracts two values and returns a resultant value. We can use it to calculate the hours and minutes of work done.

Before beginning the calculation, pre-format the cells as shown in the Why Formatting is Important… section.

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

=(D7-C7)

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

subtraction-How to Calculate Hours and Minutes for Payroll in Excel

Step 2: Hit ENTER and Drag the Fill Handle. Thus, the worked times for any particular day appear.

Fill handle

In case you calculate the payroll for any individual days, Try the following steps.

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

 =$C$4*E7*24

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.

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

Total pay formula

 Step 4: Hit ENTER and Drag the Fill Handle to bring out the total pay entries in the cells.

Total pay-How to Calculate Hours and Minutes for Payroll in Excel

Read More: How to Calculate Total Hours in Excel (9 Easy Methods)


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: Write the following formula in any blank cell (i.e., E7).

=TEXT(D7-C7,"h:mm")

In the formula,

D7-C7= value

“h:mm”=format_text

Text function-How to Calculate Hours and Minutes for Payroll in Excel

 Step 2: Press ENTER and Drag the Fill Handle to make the hours and minutes appear.

Fill handle

Step 3: Repeat Steps 3 and 4 of Method 1 with the same formula. In a moment, you’ll get the Total Pay Amount in the cells as shown in the following picture.

Total pay-How to Calculate Hours and Minutes for Payroll in Excel


Method 3: Using HOUR and MINUTE Function

Excel offers individual HOUR and MINUTE functions. 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: Type the following formula for hours in any adjacent cell (i.e., E7).

=HOUR(D7-C7)

hours and minutes-How to Calculate Hours and Minutes for Payroll in Excel

Step 2: Press ENTER then Drag the Fill Handle to make the hours appear.

Fill handle

Step 3: Repeat Steps 1 and 2 of this Method replacing the HOUR formula with the MINUTE formula. The MINUTE formula is below.

=MINUTE(D7-C7)

Fill handle

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

=(E7+(F7/60))*$C$4

F7/60, making minutes into hours and adding this with E7; we get total worked hours. Then multiplying the total worked hours with Per Hour Pay, we get the Total Pay.

Formula

Step 5: Hit ENTER after that Drag the Fill Handle to get all the Total Pay amount.

Final result-How to Calculate Hours and Minutes for Payroll in Excel


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

The TIME function takes three arguments and adds or subtracts them individually. In this case, 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)

We generally know about the arguments used in the TIME function, if you want to know further please Click on the TIME Function.

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

=TIME(HOUR(D7),MINUTE(D7),SECOND(D7))-TIME(HOUR(C7),MINUTE(C7),SECOND(C7))

time function-How to Calculate Hours and Minutes for Payroll in Excel

Step 2: Press ENTER afterward Drag the Fill Handle. All the worked time will appear in the cells.

Fill handle

Step 3: Repeat Steps 3 and 4 of Method 1 with the same formula. You’ll get all the Total Pay amount instantly as shown in the following picture.

Formuls insertion-How to Calculate Hours and Minutes for Payroll in Excel


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: Type the below formula in cell E7.

=MOD(D7-C7,1)

mod function-How to Calculate Hours and Minutes for Payroll in Excel

Step 2: Hit ENTER and Drag the Fill Handle to come up with the hours and minutes in the cells.

Fill handle

Step 3: Repeat Steps 3 and 4 of Method 1 with the same formula, you’ll get the Total Pay amount similar to the picture below.

Mod function result


Method 6: Using 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. In this case, we just calculate the hours and minutes for payroll, not the Total Pay amount. 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.

=IF(HOUR(D6-C6)>0, HOUR(D6-C6) & " hours, ","") & IF(MINUTE(D6-C6)>0, MINUTE(D6-C6) & " minutes","")

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.

if function-How to Calculate Hours and Minutes for Payroll in Excel

Step 2: Hit ENTER and Drag the Fill Handle to get all the hours and minutes in the cells similar to the image below.

If function result


Method 7: Calculating Elapsed Time

Let’s say we want to calculate the hours and minutes from a given time and right at the moment. The NOW function can do the job. In this case, we just measure the hours and minutes to any moment from a given 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()-C6

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

Elapsed time-How to Calculate Hours and Minutes for Payroll in Excel

Step 2: Hit ENTER apply the Fill Handle to get all the worked time as shown in the image.

Elapsed time result

Related Content: Excel Formula for Overtime over 40 Hours [with Free Template]


⧭Things Keep in Mind

🔄 Before applying the functions, pre-format the cell where the results will appear.

🔄 Don’t get the result values in AM/PM, instead of h:mm (i.e.Hour:Minute) format.

🔄 Excel automatically stores the subtracted value in day. Make sure you multiply 24 to the subtracted value in order to get hours.


Conclusion

In this article, we use multiple functions to calculate hours and minutes. We use functions such as TEXT, HOUR and MINUTE, TIME, MOD, IF, and NOW to measure the time differences of two given times. I hope, above-discussed methods quench your thirst as you seek. Comment if you have any queries or have something to add. You can check out my other articles on the Exceldemy website.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo