iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To calculate hours worked in Excel, subtract the start time from the end time, then format the result as a duration. For a shift recorded in B2 and C2, use =C2-B2. If a shift or total can pass midnight or exceed 24 hours, use full date-and-time entries where possible and display the result with [h]:mm. To get decimal hours for a calculation, multiply the numeric duration by 24. These formulas calculate elapsed time; they do not decide whether breaks are paid, how time should be rounded, or what payroll rules apply.
What is the dependable way to calculate hours worked in Excel?
Enter a start time and end time in separate cells, subtract the start from the end, and format the result as a duration. If the shift may cross midnight, record the dates as well as the times so Excel can distinguish the next day from the same day. This sequence keeps the arithmetic simple: enter complete inputs, calculate the elapsed duration, and choose a display format that matches the duration and how you will use it.
For a same-day shift with the start in B2 and end in C2, enter this in the duration cell:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
=C2-B2
Format the result as h:mm to display hours and minutes. For example, if the start is 9:00 AM and the end is 5:30 PM on the same day, the elapsed duration is 8 hours and 30 minutes. The cell contains a numeric duration, so it can be summed or used in later calculations. The formula does not need to convert the inputs to text or extract hour and minute pieces separately.
Popular on Amazon
To apply a number format, select the result cell or range and open the cell-formatting options. Choose a time format for an individual duration under 24 hours, or use a custom format when you need elapsed hours. Enter h:mm for a same-day duration or [h]:mm for elapsed hours that may exceed 24. The brackets around h are significant: they tell Excel to show cumulative hours rather than the hour on a 24-hour clock.
Excel stores time as a fraction of a day. That is why the subtraction returns a duration Excel can display as hours and minutes, and why multiplying that duration by a conversion factor produces another unit. Microsoft Support describes this underlying time representation and the related conversions in its guidance on the TIME function and calculating differences between times. For instance, a duration representing half a day is 12 hours; formatting controls the display, while the numeric value remains available for formulas.
A reliable worksheet therefore separates three things that are easy to confuse: the start and end inputs, the numeric duration produced by subtraction, and the way that duration is displayed. A value that looks like a clock time can still represent elapsed time, but the display must suit the task. Use h:mm for a short shift, [h]:mm for accumulated elapsed hours, or a multiplied numeric result when another formula needs decimal hours.
Recommended Free Tools
How should I calculate an overnight or multi-day shift?
Use complete start and end date-time values and subtract the start from the end. A time-only entry records a time of day, not which day it belongs to, so it cannot reliably represent every shift that crosses midnight or lasts more than one day. If you record 10:00 PM and 6:00 AM without dates, Excel cannot know from those two values alone whether the end is the next morning or whether the entries refer to different dates.
Preferred method: enter the dates and times
Put the start date and time in B2 and the end date and time in C2. For example, a start of 10:00 PM on one date and an end of 6:00 AM on the following date should be entered with both dates included. Then use:
=C2-B2
Format the duration cell as [h]:mm. For a multi-day shift, this format continues to show total elapsed hours rather than restarting the hour display after each 24-hour period. If the result is 32 hours, for example, the elapsed-hours format can show 32:00; a regular clock-style format may show only the within-day portion.
Using full date-time entries also makes the relationship between the two moments explicit in the worksheet. The end date should be the actual date of the end of the shift, not simply a repeated date from the start cell. Once both cells contain the appropriate dates and times, the same subtraction pattern works for a shift ending later that day, the next day, or after multiple days. The formula is still end minus start; the complete inputs let the result represent the intended elapsed period.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Time-only overnight calculation: use only when the data fits the assumption
If the entries contain times only, and you know every affected record represents a shift ending on the following day when its end time is earlier than its start time, you can use:
Also popular on Amazon
- Used Book in Good Condition
=MOD(C2-B2,1)
This formula treats time as a one-day cycle, so a start at 10:00 PM and an end at 6:00 AM produces an 8-hour duration. It does not tell Excel which date the shift occurred on. Do not rely on it for shifts that can span more than one day or where the end date is available; use complete date-time entries for those records.
The assumption matters because a time-only overnight formula cannot distinguish an ordinary overnight shift from a longer span that includes one or more full days. If your records contain dates, use those dates instead of asking a one-day cycle to infer the duration. If a time-only row has an end time earlier than its start time for a reason other than a shift ending the following day, the formula will still apply the cycle assumption and may return a duration that does not describe the real interval.
Why does my total time reset after 24 hours?
A regular clock-style format displays time within a 24-hour day, so a duration total may appear to wrap after 24 hours. The underlying value can still be correct; change the display format to [h]:mm to show cumulative elapsed hours.
For example, sum daily durations with:
=SUM(D2:D8)
Then format the total cell as [h]:mm if the sum can reach 24 hours. The square brackets make the hour component cumulative. Without them, a total of 27 hours may display as 3:00 because a standard time format shows the time within a day rather than total elapsed hours.
This is a display issue, not necessarily a problem with the sum formula. Check the input duration cells and the total formula separately: SUM adds the numeric durations in the range, and the total cell’s number format determines whether Excel shows a clock-style time or cumulative elapsed hours. Changing the number format does not alter the duration that the formula represents.
To make the total readable, select the total cell and apply a custom elapsed-time format of [h]:mm. The same format is useful for any individual shift that could itself exceed 24 hours. A daily total that will never reach 24 hours may display acceptably with h:mm, but a weekly or multi-day total should use the bracketed hour format so its display does not restart at a day boundary.
Microsoft Support’s guidance on adding or subtracting time in Excel covers totals beyond 24 hours and the elapsed-time format. Formatting changes how a value appears; it does not change the duration represented by the formula. When a total appears unexpectedly short, first inspect the format before replacing a correct numeric sum with a different formula.
Free tools Windows power users keep installed
One-click scans. No signup required.
How do I convert a duration to decimal hours or minutes?
Excel time values are fractions of a day, so multiply a numeric duration by the number of hours, minutes, or seconds in a day to convert its unit. Keep the formula result numeric when you plan to use it in another calculation. The subtraction produces a duration in Excel’s day-based representation; multiplying by 24, 1440, or 86400 expresses that duration as hours, minutes, or seconds.
Also popular on Amazon
| Result you need | Formula for a same-day shift | Formula when using full date-time values |
|---|---|---|
| Elapsed time as hours and minutes | =C2-B2 |
=C2-B2 |
| Decimal hours | =(C2-B2)*24 |
=(C2-B2)*24 |
| Decimal minutes | =(C2-B2)*1440 |
=(C2-B2)*1440 |
| Decimal seconds | =(C2-B2)*86400 |
=(C2-B2)*86400 |
For the decimal-hour or decimal-minute formulas, the subtraction must represent the correct elapsed duration. If the shift crosses midnight, use full date-time values rather than a time-only subtraction. Apply a suitable number format to the numeric result so the cell displays the unit you need. Decimal hours are a number rather than an hours-and-minutes display: 8.5 means eight and a half hours, not 8 hours and 50 minutes.
As a check, a duration of 8 hours and 30 minutes corresponds to 8.5 decimal hours. The decimal form is useful when a later formula needs hours as a number; the h:mm form is useful when readers need to see hours and minutes. To convert minutes directly, multiply the duration by 1440: 8 hours and 30 minutes represents 510 minutes. To express a duration in seconds, multiply by 86400.
Choose the output based on the next step in the worksheet. If you need to read a shift at a glance, retain a duration and format it as hours and minutes. If a later calculation needs a numeric count of hours, use the duration multiplied by 24 and label the column clearly as decimal hours. If a payroll system or another worksheet needs a particular unit or structure, confirm that requirement separately rather than assuming a display format determines what it accepts.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →For a same-day shift, decimal hours can be calculated directly with =(C2-B2)*24. For a shift with dates, subtract the full date-time values and multiply the result by 24. The formulas look alike, but the inputs determine whether the subtraction captures the complete interval. A time-only subtraction for an overnight shift can produce a negative difference, so use full date-time inputs or, only under the stated next-day assumption, the MOD pattern.
Should I use HOUR or TEXT to display a work duration?
Use the numeric duration and an appropriate cell number format for results that need to be added, compared, or multiplied. HOUR extracts an hour component rather than providing a dependable total-hours value for durations beyond 24 hours; TEXT returns formatted text rather than a numeric duration. These functions answer different needs from calculating and retaining the complete elapsed duration.
Why HOUR is not a total-hours formula
=HOUR(C2-B2) can appear to work for a short shift, but it is not a reliable way to get total elapsed hours when a duration can exceed 24 hours. Microsoft notes that the HOUR component cannot exceed 24. Use the full duration with an elapsed-time format, or multiply the duration by 24 to calculate decimal hours.
For example, if a total represents 27 elapsed hours, extracting an hour component is not the same as reporting all 27 hours. It returns a component associated with the time value rather than a cumulative total. To see the accumulated duration, format the value as [h]:mm; to produce numeric hours, multiply the numeric duration by 24. The distinction is especially important when adding multiple shifts, because the sum may pass 24 hours even when every individual shift is shorter.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11When TEXT is useful
TEXT can turn a duration into a display string, for example =TEXT(C2-B2,"h:mm"). That can be useful for a final presentation, but the result is text, not a numeric duration. Do not substitute it for a value that needs to be summed or multiplied. If you need both a readable display and calculations, keep the numeric duration in its own cell and format that cell.
Also popular on Amazon
Think of number formatting and TEXT as different choices. Applying a number format changes how a numeric cell appears while leaving the numeric duration available to formulas. Wrapping a value in TEXT creates formatted text. Use that only when the output is intended as a display string and will not need to participate as a duration in later arithmetic. For a working timesheet, retaining numeric values is usually the more flexible option.
Which formula and format should I choose?
Choose based on what the input cells record, whether the duration can cross a day boundary, and whether the output is for calculation or display. The right formula depends first on input completeness; the right format depends on the duration range and desired output unit.
| Situation | Input and formula | Recommended display or result |
|---|---|---|
| Same-day shift under 24 hours | Time-only start and end; =C2-B2 |
h:mm |
| Known overnight shift with time-only entries | Time-only entries; =MOD(C2-B2,1), only when every affected record ends the following day |
[h]:mm if needed for an accumulated total |
| Overnight or multi-day shift with dates available | Full date-time entries; subtract end minus start | [h]:mm |
| Decimal-hour calculation | Numeric duration multiplied by 24 | Numeric hours |
| Final display text only | TEXT applied to a duration |
Formatted text; not a reusable numeric duration |
Use the first row when a record is a same-day interval and the result stays below 24 hours. Choose the time-only overnight row only when the data convention really means that an earlier end time belongs to the next day. If actual dates are known, use them; complete date-time inputs provide the information that a time-only value lacks.
The output choice is a separate decision. A duration displayed as [h]:mm stays a numeric duration and is useful for totaling elapsed hours. Decimal hours are also numeric, but use a different unit and are useful when a later calculation expects a numeric hour quantity. A TEXT result is for display, not reusable arithmetic. Label the output column to make the selected unit clear to anyone who uses the sheet.
How can I keep the worksheet usable for payroll calculations?
Separate time arithmetic from payroll decisions. A spreadsheet can calculate elapsed time, but a formula alone does not establish how breaks are treated, what rounding method is appropriate or lawful, how overtime is determined, or what a payroll system will accept. Keep the worksheet’s job narrow: represent the recorded start and end moments, calculate their elapsed duration, and preserve the result in a form that can be checked and reused.
- Store the inputs clearly. Keep start and end values in distinct cells. Include dates for overnight or multi-day work so the entries identify the actual start and end moments.
- Keep calculated durations numeric. Use number formatting to control how numeric results appear. Avoid converting values to text before totals or downstream calculations.
- Make units explicit. A value displayed as
h:mmis hours and minutes; a value multiplied by 24 is decimal hours. Do not mix the two representations without a deliberate conversion. - Check the relevant rules separately. Confirm break treatment, rounding, overtime, recordkeeping, and other payroll requirements with the applicable jurisdiction and employer policy.
- Check import requirements separately. Payroll systems can have their own accepted fields and formats. Confirm the specific system’s requirements rather than assuming an Excel formula or display format will satisfy them.
Before relying on a total, verify that each row uses the intended inputs and formula pattern: same-day time-only entries can use direct subtraction; known next-day time-only entries require the stated cycle assumption; and entries with dates should use full date-time subtraction. Check the displayed total with an elapsed-hours format if it could exceed 24 hours. These checks help catch a mismatch between the recorded interval and the displayed value without confusing a spreadsheet calculation with a payroll rule.
There is no universal formula in these patterns that decides whether a break is paid or deducted, which rounding practice applies, how overtime is treated, or which records must be retained. Those questions depend on jurisdiction and employer policy. Similarly, an Excel number format does not guarantee that a particular payroll provider accepts the workbook or its values. Confirm the applicable rules and the specific provider’s import requirements independently.
The Microsoft Support pages cited for Excel’s time arithmetic explain spreadsheet calculations and formatting, not payroll compliance. No single formula can make a worksheet legally compliant across jurisdictions or ensure a particular payroll system will accept its output.
Also popular on Amazon
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Frequently Asked Questions
Why does Excel show a number like 0.375 instead of a time?
That is the stored numeric value for a time or duration expressed as a fraction of a day. Apply a time or duration number format to display it as hours and minutes; the underlying numeric value remains available for calculations.
Can I enter a shift as 8.5 hours instead of 8:30?
Yes, but those are different representations: 8.5 is decimal hours, while 8:30 is hours and minutes. Keep the unit clear in the column label and avoid treating one representation as the other in later formulas.
Will an Excel time formula tell me whether an employee earned overtime?
No. A time formula calculates elapsed time, not eligibility or overtime treatment. Those decisions depend on the applicable rules and policy, which must be checked separately from the spreadsheet arithmetic.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsHow do I calculate overnight hours in Excel?
If dates are available, enter full start and end date-time values, subtract the start from the end, and format the result as [h]:mm. For time-only data, =MOD(C2-B2,1) works only when you know each affected shift ends the following day; it cannot represent multi-day spans reliably.
Why does my total time reset after 24 hours?
A regular clock-style format can wrap after 24 hours. Keep the numeric sum, such as =SUM(D2:D8), and format its cell as [h]:mm to show cumulative elapsed hours.
Should I use TEXT to make my duration look like hours and minutes?
Use TEXT only when you want a final display string. It returns text rather than a numeric duration, so a number format is preferable when the value needs to be summed, compared, or multiplied later.
The Bottom Line
For dependable Excel time math, subtract complete start and end values, use [h]:mm for elapsed totals that may exceed 24 hours, and multiply numeric durations by 24 when you need decimal hours. Keep duration values numeric for further calculations, and verify break, rounding, overtime, recordkeeping, and payroll-import requirements separately from the spreadsheet formulas.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
