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 Operators (i.e., Subtraction (-)) can calculate time differences regarding different scenarios.

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.

**Table of Contents**Expand

**Why Formatting Is Important 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 simply subtract times without prior formatting the cells you’ll get something as shown below.

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 the **Number Format** and **13:30** as the **Type**. Then, Click **OK**.

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

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

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

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

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

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

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.

** ****Step 4: **Hit **ENTER** and Drag the **Fill Handle** to bring out the total pay entries in 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:** 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**

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

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

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

**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)`

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

**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)`

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

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

**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. 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))`

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

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

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

`=MOD(D7-C7,1)`

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

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

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

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

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

**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**).

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

**⧭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.

**Download Excel Workbook**

**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 between two given times. I hope the above-discussed methods quench your thirst as you seek. Comment if you have any queries or have something to add.

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