To calculate hours worked in Excel, subtract the start time from the end time; for a shift that might pass midnight, use =MOD(B2-A2,1). Format the result as [h]:mm to show elapsed hours and minutes, or multiply the duration by 24 to get decimal hours for payroll calculations.
How Excel stores times
Excel stores a time as a fraction of a 24-hour day: noon is one-half, for example. Subtracting one time from another therefore produces a fraction, not a number of hours. A result of 0.375 represents nine hours because 0.375 × 24 = 9. Excel can display that fraction as a clock time or as a duration, depending on the cell’s number format.
This distinction matters because a clock format such as h:mm is designed to show the time of day. A total of 27 hours may display as 3:00, which looks like three hours. The custom duration format [h]:mm displays the total as 27:00 instead. The square brackets tell Excel to keep counting elapsed hours rather than restarting the display at 24.
Enter times as recognizable values such as 8:30 AM or 5:15 PM, rather than as text such as “eight thirty.” If a value is stored as text, Excel may not subtract it as expected. You can check alignment as a quick clue—text is often left-aligned by default, while numeric time values are usually right-aligned—but alignment alone is not conclusive.
Calculate a shift that ends the same day
Set up a simple timesheet with the shift start in A2 and the shift end in B2. For an 8:00 AM start and a 4:30 PM finish, the elapsed time is 8 hours 30 minutes. The ordinary subtraction formula is:
With the start time in A2 and the end time in B2, enter =B2-A2. For the example 8:00 AM to 4:30 PM, the result is 8:30 when the result cell uses the [h]:mm format.
In Microsoft 365 for Windows, select the result cell, press Ctrl+1 to open Format Cells, choose Number > Custom, and enter [h]:mm in the Type box. On a Mac, Command+1 opens Format Cells; the same custom format works. If the result appears as a decimal such as 0.3541667, the calculation may be correct—the cell simply needs a duration or time format.
For a small table, put the formula in the first result row and fill it down using the fill handle at the cell’s lower-right corner. Each copied formula adjusts its row reference automatically, so the next row uses A3 and B3, then A4 and B4.
Handle overnight shifts
When a shift starts before midnight and ends after it, direct subtraction can produce a negative value. The MOD formula treats the end time as occurring later on the next day when the clock time is earlier than the start time.
With the start time in A2 and the end time in B2, use =MOD(B2-A2,1). For a 10:00 PM start and a 6:00 AM end, it returns 8:00 when displayed as [h]:mm.
This method is useful for ordinary shifts shorter than 24 hours when the cells contain times only. It has an important limitation: the times 8:00 AM and 8:00 AM produce zero, not a 24-hour shift. Times alone do not tell Excel whether the end was later that day, the next day, or several days later. If shifts can last 24 hours or more, enter full dates and times so the start and end identify actual points on the calendar.
For example, enter a complete start date and time in A2 and a complete end date and time in B2. With those date-time values, use =B2-A2. Format the result as [h]:mm. A shift from November 3 at 10:00 PM to November 4 at 6:00 AM displays as 8:00. Including dates is also a good practice when shifts may cross weekends, holidays, or reporting periods.
Subtract unpaid breaks
A basic start-to-end calculation measures the full span, including any break in the middle. To deduct an unpaid break entered as a number of minutes, add a Break Minutes column. This layout keeps break entry straightforward and permits more than one break by entering the combined unpaid minutes.
With start time in A2, end time in B2, and total unpaid break minutes in E2, enter =MOD(B2-A2,1)-E2/1440. A 9:00 AM to 5:30 PM shift with a 30-minute unpaid break returns 8:00, or 8 hours worked.
There are 1,440 minutes in a day, so dividing minutes by 1,440 converts the break to Excel’s time units. Use [h]:mm to display the net duration. If the break is paid, do not subtract it. If breaks are entered as start and end times instead, calculate each break’s length separately and subtract the total; that approach needs care when a break crosses midnight.
Check that the break total is not longer than the shift. Otherwise, the net duration can be negative, which may display as a row of number signs or an unexpected value under the workbook’s date system. A formula cannot determine whether a break is legally unpaid or whether a particular break rule applies; it only performs the arithmetic represented by the cells.
Show decimal hours for payroll or reports
Some payroll systems and reports use decimal hours instead of hours and minutes. In that format, 8 hours 30 minutes is 8.5 hours, not 8.30. The minutes are a fraction of an hour: 30 ÷ 60 = 0.5.
With the start time in A2 and end time in B2, use =24*MOD(B2-A2,1) to return decimal hours for a shift that may cross midnight. For 10:00 PM to 6:00 AM, the result is 8. Format the result as Number, not as a time. To include an unpaid break recorded in minutes in E2, use =24*(MOD(B2-A2,1)-E2/1440). The earlier 9:00 AM to 5:30 PM example with a 30-minute unpaid break returns 8.5.
A decimal result can be rounded for display, but rounding should reflect the rules used by the organization or payroll system. If the start and finish times are rounded before calculating, the total can differ from a calculation made from exact times and rounded only at the end. Keep the original entries if you need to audit how a total was produced.
Total hours across shifts
Once each shift’s duration is calculated, add the duration cells to get a period total. Use duration formatting for that total as well; otherwise, a week longer than 24 hours can appear to wrap around.
With daily durations in C2:C6, enter =SUM(C2:C6) in the total cell and format it as [h]:mm. If the five shifts are 8:00, 7:30, 8:15, 8:00, and 6:00, the result is 37:45. For a decimal total, multiply the sum by 24: with the same duration values in C2:C6, use =24*SUM(C2:C6), which returns 37.75.
To keep a timesheet easy to review, use separate columns for date, start, end, unpaid break minutes, net hours, and notes. Give the total its own row and label units clearly. A value of 37:45 means 37 hours and 45 minutes, while 37.75 means decimal hours; confusing the two can lead to incorrect interpretation when totals are copied into another system.
Overtime and rounding considerations
Excel can split a calculated duration at a threshold, but the threshold and the period it applies to must come from the relevant workplace policy or applicable rules. Do not assume that every worker has the same daily or weekly overtime calculation. A spreadsheet formula only applies the cutoff you specify.
For a purely illustrative daily calculation, suppose net decimal hours are in C2 and the chosen cutoff is eight hours. With decimal hours in C2, =MIN(C2,8) returns up to eight regular hours; for 9.5 hours, it returns 8. With decimal hours in C2, =MAX(C2-8,0) returns hours above that cutoff; for 9.5 hours, it returns 1.5. These examples demonstrate arithmetic, not a determination of which overtime rule applies.
Rounding is another policy choice, not a formatting fix. Changing the number of displayed decimal places does not necessarily change the stored value or the total. If a process requires rounding, decide whether to round each punch, each shift, or the final total, and apply the same documented method consistently. Preserve actual start and end entries so you can compare them with rounded results.
Common errors and how to fix them
- A result shows ####. The column may be too narrow, or a negative time may be displayed under the workbook’s date system. Widen the column first; if the value is negative, check the start, end, and break entries.
- An overnight duration is negative. Replace direct subtraction with
=MOD(B2-A2,1)when the inputs are times only and the shift is under 24 hours. For longer periods, use full date-time entries and subtract them directly. - A weekly total shows only the remainder after 24 hours. Apply the custom duration format
[h]:mmto the total cell instead of a clock-time format. - The answer is a decimal fraction. Excel may be showing its stored time value. Use
[h]:mmfor an elapsed-time display or multiply by 24 and use a number format for decimal hours. - A formula is treated as text. Check that the cell is not formatted as Text and that the formula begins with an equals sign. After changing a text-formatted cell to General, re-enter the formula.
- Totals are unexpectedly too high or low. Confirm whether breaks are paid, whether the break minutes are entered in minutes rather than hours, and whether the same shift or break has been included twice.
Also check for missing entries before relying on a report. A blank end-time cell can make a subtraction formula look like a valid zero or an unexpected duration. A practical timesheet review should flag incomplete rows rather than treating every calculated value as final.
Frequently Asked Questions
How do I calculate hours worked in Excel with a lunch break?
Subtract the unpaid break from the shift duration. If A2 is the start time, B2 the end time, and E2 contains unpaid break minutes, use =MOD(B2-A2,1)-E2/1440 and format the result as [h]:mm.
How do I calculate hours worked as a decimal?
Multiply the time duration by 24. For times in A2 and B2, =24*MOD(B2-A2,1) returns decimal hours, including for a typical overnight shift. Format the answer as a number.
Why does Excel show 0:00 or a negative time for an overnight shift?
Direct subtraction sees an end time earlier than the start time as a negative difference. Use the MOD formula for times-only shifts under 24 hours. If the shift might last 24 hours or longer, enter dates with the times so Excel can distinguish the actual elapsed period.
How do I stop total hours from resetting after 24 hours?
Format the total cell with the custom duration format [h]:mm. A standard clock format can wrap after a full day, while the bracketed hour format continues counting elapsed hours.
Does 8:30 mean 8.30 hours?
No. In an hours-and-minutes display, 8:30 means 8 hours and 30 minutes, which equals 8.5 decimal hours. The minutes must be divided by 60 to convert them into a decimal fraction of an hour.
Related
- Compare Lists in Excel: Find Matches and Differences
- Combine Two Cells in Excel and Keep Both Text
- Combine in Excel: Text, Cells, and Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Midpoint of Dates: Formula and ExamplesTo find the midpoint between two dates in Excel, put the earlier date in A2 and the later…
- Excel Filter Between Text Ranges: Steps and LimitsYes. In Excel for Microsoft 365 on Windows, open the column’s filter menu, choose Text Filters > Custom…
- Xlookup vs vlookup?XLOOKUP defaults to exact matches and can return values from either side. See where VLOOKUP still fits and…
- Excel INDIRECT: Link to Another Tab with a FormulaTo refer to a cell on another worksheet using a sheet name stored in A1, enter =INDIRECT("'"&A1&"'!B2"). If…
- How to Convert English to Hindi in Excel with FormulasTo convert English text in Excel to Hindi, enter the text in a cell and use Microsoft 365’s…
- Excel Filter Only Show Duplicates: A Practical GuideExcel does not have a built-in AutoFilter command that directly displays only duplicate values, but you can add…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.