Calculating hours and minutes for payroll in Excel is a crucial task for HR teams, accountants, and business managers. Accurate time tracking ensures employees are paid correctly, helps avoid payroll errors, and simplifies reporting. Excel offers a variety of functions and formulas to handle both simple and complex time calculations efficiently.
In this article, we will explore seven practical methods to calculate hours and minutes for payroll in Excel. From straightforward subtraction to more advanced techniques using TEXT, HOUR, MINUTE, TIME, MOD, and IF functions. Let’s get started.
Steps to calculate hours and minutes for payroll in Excel:
➤ Ensure Start Time is in B2 and End Time in C2.
➤ In a new column (e.g., E2), enter the formula:
=MOD(C2-B2,1)*24 – this gives decimal hours worked (8:30 becomes 8.5), even for night shifts.
➤ In F2, multiply by the hourly rate in D2 to get pay: =E2*D2
➤ Press Enter and drag down for all rows.
The screenshot shows the text-display version of the formula from Method 2.
Basic Subtraction in Excel to Calculate Hours and Minutes Worked
For payroll, the simplest way to find how many hours an employee worked is by subtracting the start time from the end time. This method gives a direct measure of worked hours, ideal for straightforward daily shifts or standard office hours. Accurate subtraction ensures payroll is computed correctly and serves as the basis for more advanced calculations.
We’ll use the following dataset:
Steps:
➤ Ensure your dataset includes Start Time in B2, End Time in C2, and Hourly Rate in D2.
➤ In a new column (e.g., E2), enter the formula:
=C2-B2
➤ Open the Format Cells tab using Ctrl + 1 . Select Custom >> Type [h]:mm to display hours and minutes correctly and click OK.
![Format Cells Custom type set to [h]:mm for hours worked](https://excelinsider.com/wp-content/uploads/2025/11/4-how-to-calculate-hours-and-minutes-for-payroll-in-excel.png)
➤ To calculate pay, multiply the elapsed time by the hourly rate in F2 cell:
=(C2-B2)*D2*24
➤ Press Enter and drag the fill handle down for all rows.
For a shift that ends after midnight this formula returns a negative amount – 22:00 to 06:00 at $20 an hour gives -320. Use =MOD(C2-B2,1)*24*D2 instead, which returns 160. There is more on night shifts in how to calculate hours between two times after midnight.
Insert IF Function to Display Hours and Minutes in Text Format
For timesheets that need clear, readable text such as “8 hours 30 minutes”, combining IF with HOUR and MINUTE functions produces results that are easy to interpret. Two limits apply. The result is text, so it cannot be multiplied by an hourly rate – use Method 4 or Method 6 for pay. And HOUR and MINUTE return an error when a shift crosses midnight, unless you replace each C2-B2 with MOD(C2-B2,1). For other ways to display a duration, see how to calculate hours and minutes in Excel.
Steps:
➤ Ensure Start Time is in B2 and End Time in C2.
➤ In a new column (e.g., E2), enter the formula:
=IF(HOUR(C2-B2)>0, HOUR(C2-B2) & " hours ","") & IF(MINUTE(C2-B2)>0, MINUTE(C2-B2) & " minutes","")
➤ Press Enter and drag down for all rows.
Applying the TEXT Function for Readable Hours and Minutes
When reporting payroll or presenting timesheets, a readable format can be more user-friendly than raw Excel time values. The TEXT function converts the difference between start and end times into a clear “Hours:Minutes” format, making it easier to review and present work durations.
Steps:
➤ Ensure your dataset includes Start Time in B2 and End Time in C2.
➤ In a new column (e.g., E2), enter the formula:
=TEXT(C2-B2,"h:mm")
➤ Press Enter and drag down using the AutoFill handle.
Now the former part represents hours worked and the later part shows the number of minutes.
Convert Hours and Minutes to Decimal Hours with HOUR and MINUTE
Sometimes payroll requires knowing hours and minutes individually, for example, to calculate fractional pay accurately. Using the HOUR and MINUTE functions allows granular control and flexibility when computing elapsed time and converting it into a total payable amount.
Payroll needs hours as a decimal, not as hours and minutes, so the minutes have to be divided by 60. Thirty minutes is 0.5 of an hour, which makes 8 hours 30 minutes equal to 8.5 hours. The common values, rounded to two decimals:
| Minutes | Decimal hours |
|---|---|
| 5 | 0.08 |
| 10 | 0.17 |
| 15 | 0.25 |
| 20 | 0.33 |
| 30 | 0.50 |
| 40 | 0.67 |
| 45 | 0.75 |
| 50 | 0.83 |
The formula below does the same conversion for you. It works for shifts that end on the same day; for a night shift, use =MOD(C2-B2,1)*24*D2, because HOUR and MINUTE return an error on a negative time.
Steps:
➤ Ensure your dataset includes Start Time in B2, End Time in C2, and Hourly Rate in D2.
➤ Extract hours by entering this formula in E2 cell:
=HOUR(C2-B2)
➤ Extract minutes by entering this formula in F2 cell:
=MINUTE(C2-B2)
➤ To calculate total pay including minutes as a fraction of an hour, enter this formula in G2 cell:
=(HOUR(C2-B2) + MINUTE(C2-B2)/60) * D2
➤ Press Enter and drag down for all employees using the AutoFill handle.
Using the TIME Function to Ignore the Date Part
The TIME function rebuilds each time from its hour, minute and second, which throws away any date attached to it. On plain times like the ones in this dataset it gives exactly the same answer as Method 1. It is useful when your start and end cells hold full date-and-time stamps from a clock-in system and you only want the clock-time difference. It does not handle shifts that cross midnight – with date stamps it actually breaks them, turning a correct 8:00 into a negative value. For night shifts, use Method 6.
Steps:
➤ Ensure Start Time is in B2 and End Time in C2.
➤ Select your output cell like E2 >> Open the Format Cells tab using Ctrl + 1 . Select Custom >> Type [h]:mm to display hours and minutes correctly and click OK.
![Output cell formatted as [h]:mm before the TIME formula](https://excelinsider.com/wp-content/uploads/2025/11/10-how-to-calculate-hours-and-minutes-for-payroll-in-excel.png)
➤ In a new column (e.g., E2), type the formula:
=TIME(HOUR(C2), MINUTE(C2), SECOND(C2)) - TIME(HOUR(B2), MINUTE(B2), SECOND(B2))
➤ Press Enter and drag down for all rows.
Now the former part represents hours worked and the later part shows the number of minutes.
Handling Overnight Shifts with MOD Function
Employees working late-night or overnight shifts can produce negative results when using standard subtraction. The MOD function solves this by ensuring elapsed time is always positive, making payroll calculations accurate regardless of shift timing.
Steps:
➤ Ensure Start Time is in B2 and End Time in C2.
➤ Select your output cell like E2 >> Open the Format Cells tab using Ctrl + 1 . Select Custom >> Type [h]:mm to display hours and minutes correctly and click OK.
![Output cell formatted as [h]:mm before the MOD formula](https://excelinsider.com/wp-content/uploads/2025/11/12-how-to-calculate-hours-and-minutes-for-payroll-in-excel.png)
➤ In a new column (e.g., E2), type the formula:
=MOD(C2-B2,1)
➤ Press Enter and drag down for all employees.
Now the former part represents hours worked and the later part shows the number of minutes.
Calculating Elapsed Time Using NOW Function
When payroll or attendance requires monitoring ongoing shifts, the NOW function can calculate elapsed hours up to the current moment. This is particularly useful for live tracking or dynamic dashboards. We’ll use the following dataset:
Steps:
➤ Ensure Start Time is in B2.
➤ Select your output cell like E2 >> Open the Format Cells tab using Ctrl + 1 . Select Custom >> Type [h]:mm to display hours and minutes correctly and click OK.
![Output cell formatted as [h]:mm before the NOW formula](https://excelinsider.com/wp-content/uploads/2025/11/15-how-to-calculate-hours-and-minutes-for-payroll-in-excel.png)
➤ In a new column (e.g., D2), enter the formula:
=MOD(NOW(),1)-B2
➤ Press Enter and drag down for all rows.
Now this displays the worked hours till the present time of the day. NOW recalculates every time the sheet changes, so the figure keeps moving – copy and paste it as a value when the shift ends. And if a shift started before midnight, this version goes negative after 12:00 AM; use =MOD(NOW()-B2,1) instead, which returns 4:00 for a 22:00 start checked at 02:00.
Downloadable Resources
Get our practice workbook or necessary files that we have worked with to prepare this article.
Calculate Hours and Minutes for Payroll.xlsx
Frequently Asked Questions
Can Excel calculate work hours across midnight shifts?
Yes, using the MOD function, Excel can handle overnight shifts. By applying =MOD(EndTime-StartTime,1), negative values are avoided, ensuring accurate hours and minutes even when employees work past midnight.
How do I include lunch breaks in payroll calculations?
To account for breaks, subtract the break duration from total hours worked. For example, =(EndTime-StartTime)-BreakTime calculates net work hours when the break is entered as a time such as 0:30. If you type the break as 0.5 instead, Excel reads it as half a day and subtracts 12 hours. See our Excel formula to calculate hours worked minus lunch for both layouts. Format as [h]:mm to display hours and minutes correctly.
Which method is best for readable time formats?
The TEXT function is ideal for displaying hours and minutes in a clear format. For example, =TEXT(EndTime-StartTime,"h:mm") converts Excel’s time values into a user-friendly “hours:minutes” format.
Which formula calculates total pay from hours worked and hourly rate?
Multiply them: =C2*D2, with hours worked in C2 and the hourly rate in D2. 8.5 hours at $20 returns $170. Adding them (=C2+D2) or summing them gives 28.5, and averaging gives 14.25, neither of which is pay. One catch: this assumes C2 holds hours as a decimal such as 8.5. If C2 holds a time such as 8:30, Excel stores it as a fraction of a day and the result is only $7.08 – use =C2*24*D2 instead.
How can I track work hours in real-time?
Use the NOW function: =MOD(NOW()-StartTime,1), formatted as [h]:mm. The MOD matters – NOW includes today’s date, so plain NOW()-StartTime against a start time with no date returns over a million hours. This is useful for ongoing shifts or live tracking.
Wrapping Up
In this tutorial, we covered multiple ways to calculate hours and minutes for payroll in Excel, ensuring accuracy and flexibility for different work scenarios. Whether you’re dealing with standard shifts, overnight work, or live tracking, these methods allow you to compute employee hours efficiently. To add daily hours into a weekly total that can pass 24 hours, see how to calculate total hours in Excel. Feel free to download the practice file and share your feedback.
