A clock-in time in one cell, a clock-out time in another, and a third cell that just needs
to show "how many hours." It looks like a one-key answer, until =B2-A2 comes
back showing another time like 8:00 AM instead of a number, or a night shift that starts
at 10 PM and ends at 6 AM returns a negative result instead of eight hours.
Excel handles time math well once you know that a time is really just a fraction of a day under the hood. This guide covers the plain subtraction method, the fix for shifts that cross midnight, and how to display the result the way a timesheet or invoice actually needs it.
The simplest way to find the number of hours between two times in Excel is
=(B2-A2)*24, which converts the time difference into a decimal hour count as
long as the result cell is formatted as a number. For a shift that crosses midnight, use
=MOD(B2-A2,1)*24 instead, which corrects the negative result that plain
subtraction produces.
What is an hours-between-times formula, and what are the options?
"Hours between two times" can mean three slightly different things depending on what the spreadsheet needs to show, and Excel has a separate approach for each one.
- Direct subtraction, multiplied by 24 (
=(B2-A2)*24) returns a decimal hour count, which works as long as the end time falls later in the same day than the start time. - MOD-wrapped subtraction (
=MOD(B2-A2,1)*24) handles shifts that cross midnight, where the clock-out time is technically earlier in the day than the clock-in time. - TEXT formatting displays the same underlying difference as a readable "h hrs mm min" string instead of a decimal, which is closer to how a timesheet or invoice usually presents duration.
All three read the same two time cells; the difference is entirely in how the rollover past midnight is handled and how the result is displayed.
Why the right formula matters
Picking the wrong approach for the situation doesn't throw an error — it just quietly gives a technically-correct number that answers the wrong question. That shows up in a few common places:
- Payroll and timesheets. An overnight shift calculated with plain subtraction returns a negative number instead of the hours actually worked, breaking any total that sums the column.
- Billing by the hour. A consultant or contractor invoice needs an exact decimal hour count, not a rounded-off guess, or the client-facing total won't match the hours actually logged.
- Call center and support metrics. Average handle time and response time reports depend on consistent hour math across every row, including the handful of tickets that span midnight.
- Scheduling and shift planning. A roster that silently miscounts overnight shifts can understaff a night without anyone noticing until coverage is already short.
Step-by-step: calculating hours between two times
Method 1: Same-day hours (direct subtraction)
- Enter both times in separate cells. Make sure both are stored as real times, not text — for example, A2 as the start time and B2 as the end time.
- Click an empty cell for the result. This is where the hour count will appear.
-
Type the subtraction formula, multiplied by 24.
=(B2-A2)*24
- Format the result cell as Number. If the cell shows a time instead of a decimal, change its format to Number or General so it displays the hour total correctly.
Method 2: Overnight shifts (MOD function)
-
Wrap the subtraction in MOD.
=MOD(B2-A2,1)*24
- Press Enter. MOD adds a full 24-hour day back whenever the raw subtraction would otherwise be negative, so a 10 PM to 6 AM shift correctly returns 8 rather than -16.
- Use this version by default on any timesheet where shifts might cross midnight, since it also works correctly for same-day times.
Method 3: Readable hours and minutes (TEXT function)
-
Wrap the time difference in TEXT to format it as a duration string instead of a decimal.
=TEXT(MOD(B2-A2,1),"h\"hrs \"mm\"min\"")
- Press Enter. The formula displays the same underlying difference as something like "8hrs 30min" instead of 8.5.
Common mistakes with time difference formulas
1. Forgetting to reformat the result cell
Subtracting two times often inherits Time formatting from one of the source cells, so Excel displays the difference as a clock time, like 8:00 AM, instead of a plain decimal such as 8.
2. Not accounting for shifts that cross midnight
=(B2-A2)*24 assumes the end time is later in the same day than the start
time, so any overnight shift returns a negative number unless the formula is wrapped in
MOD.
3. Storing one of the times as text
A time typed or imported in a format Excel doesn't recognize is stored as text, and both subtraction and MOD return a #VALUE! error against a text cell instead of calculating a difference.
4. Rounding the decimal hour count too early
Rounding a decimal hour result before summing a full timesheet column compounds small errors across many rows, so any rounding for display should happen after the totals are calculated, not before.
TIMEVALUE(), before running the formula again.
Real-world formula examples
A few common scenarios and the formula each one needs.
Subtraction vs MOD vs TEXT
A side-by-side look at how the three approaches compare in Excel.
| Factor | Direct subtraction ×24 | MOD function | TEXT function |
|---|---|---|---|
| Output | Decimal hours | Decimal hours | Readable "h hrs m min" text |
| Handles overnight shifts | No | Yes | Yes, when combined with MOD |
| Easy to sum for totals | Yes | Yes | No, text output |
| Best for | Simple same-day time gaps | Payroll, timesheets, rotating shifts | Client-facing invoices, readable reports |
Skip the spreadsheet: free hours-between-times calculator
If you just need the gap between two times and don't want to open Excel, the Rebrixe Hours Between Times Calculator works entirely in your browser: enter a start and end time, get the exact difference in hours and minutes instantly, including overnight shifts. No formulas, no account, no data sent anywhere.