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.

In the dataset, we have a partial month (i.e., **Decembe**r) 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**hide

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

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 **Number Format** and **13:30** as **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 suppose to.

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

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

** ****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: ****Excel Formula To Calculate Time Worked**

**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 all the **Total Pay** amount.

**Read More: ****How to Calculate Time in Excel (16 Possible Ways)**

**Similar Readings**

**How to Do Payroll Reconciliation in Excel (with Easy Steps)****Excel Formula for Overtime over 8 Hours (4 Examples)****How to Calculate Total Hours Worked in a Week in Excel (Top 5 Methods)****Excel Formula for Overtime over 40 Hours [with Free Template]****How to Make a Payroll System in Microsoft Excel with Payslip**

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

**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 Generate Payroll in Excel VBA (with Easy Steps)**

**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: ****How to Create Payroll Calculator in Excel (with Easy Steps)**

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

**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: Man Hours Calculation in Excel (6 Useful Methods)**

**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** apply the **Fill Handle** to get all the worked time as shown in the image.

**Read More: ****How to Calculate Elapsed Time in Excel (8 Ways)**

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