A shift starts at 9:15 PM and ends at 6:00 AM. A meeting spans two time zones. A timesheet needs total hours worked, not just clock-in and clock-out. The moment "how much time passed?" involves an overnight shift or needs to feed into payroll, simple subtraction starts breaking — the result goes negative, or Excel shows a full date-time stamp instead of a clean duration.
Time difference is easy once you match the method to the situation: same-day subtraction, an overnight-safe formula, or a decimal-hours conversion for payroll. This guide walks through each one, the mistakes that trip people up, and which to reach for.
To find the difference between two times on the same day, subtract the start time from
the end time and format the result as time (h:mm). If the shift crosses midnight, use
=MOD(B2-A2,1) instead, which automatically adds a day back in so the result
doesn't come out negative.
What is a time difference calculation, and what are the options?
"Time difference" usually means one of three things, depending on what happens between the start and end time.
- Direct subtraction (
=B2-A2) works when both times fall on the same day and the end time is later than the start time. - The MOD formula (
=MOD(B2-A2,1)) handles overnight spans, correcting for the case where the end time is technically "earlier" than the start time because it rolled past midnight. - Decimal hours (
=(B2-A2)*24) converts a duration into a plain number like 7.5, which is what payroll systems and hourly-rate calculations usually expect.
Each approach reads the same two time values; the difference is in how midnight is handled and whether the output needs to look like a clock duration or a plain number.
Why the right method matters
Picking the wrong method for the situation doesn't throw an error — it just quietly hands back a number that looks plausible but is wrong. That shows up in a few common places:
- Payroll and timesheets. Overnight shifts calculated with plain subtraction come out negative, and an hourly rate multiplied against a raw duration instead of decimal hours overpays or underpays every affected shift.
- Call center and support metrics. Average handle time or response time looks wildly off if a call that crosses midnight subtracts into a negative duration instead of the correct span.
- Meeting and travel scheduling. Comparing times across zones without adjusting for the offset first makes a meeting look like it starts hours earlier or later than it actually does locally.
- Billing by the hour. Freelance or contractor invoices that track logged time need decimal hours, not a raw clock duration, or the invoice total won't match the hours actually billed.
Step-by-step: calculating time differences
Method 1: Same-day duration (direct subtraction)
- Enter both times in separate cells. Store them as real time values, 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 duration will appear.
-
Type the subtraction formula.
=B2-A2
- Format the result cell as Time (h:mm). If it shows a full date-time instead of a short duration, change the cell format to a custom h:mm pattern.
Method 2: Overnight shifts (MOD formula)
-
Wrap the subtraction in MOD.
=MOD(B2-A2,1)
- Format the result as Time (h:mm). MOD adds a full day back in whenever the end time crosses midnight, so the duration comes out positive and correct either way.
Method 3: Decimal hours for payroll
-
Multiply the (MOD-corrected) difference by 24.
=MOD(B2-A2,1)*24
- Format the result cell as Number. This converts the fractional day into total decimal hours, like 7.5, which multiplies cleanly against an hourly rate.
Common mistakes with time difference formulas
1. Not accounting for overnight shifts
Plain subtraction assumes the end time is later in the same day, so a shift running past
midnight returns a negative duration unless it's wrapped in MOD(...,1).
2. Leaving the result cell formatted as date-time
Subtracting two time cells often inherits date-time formatting from one of the source cells, showing a full timestamp instead of a short h:mm duration.
3. Forgetting to convert to decimal hours before applying a rate
Multiplying an hourly rate directly against a raw time duration (which Excel stores as a fraction of a day) gives a tiny, meaningless number — the duration needs to be multiplied by 24 first to get usable decimal hours.
4. Comparing times across zones without adjusting the offset
Subtracting two local times from different time zones directly, without first converting both to a common reference, gives a gap that's off by however many hours separate the zones.
[h]:mm with square brackets — that tells the spreadsheet to
keep accumulating hours instead of rolling over like a clock.
Real-world examples
A few common scenarios and the formula each one needs.
Direct subtraction vs MOD formula vs decimal hours
A side-by-side look at how the three approaches compare.
| Factor | Direct subtraction | MOD formula | Decimal hours |
|---|---|---|---|
| Output | Clock duration (h:mm) | Clock duration (h:mm) | Plain number (e.g. 7.5) |
| Handles overnight shifts | No | Yes | Yes, if MOD-wrapped |
| Ready for rate math | No | No | Yes |
| Best for | Same-day durations, meeting lengths | Night shifts, overnight call logs | Payroll, hourly invoicing |
Skip the formulas: free time difference calculator
If you just need the gap between two times and don't want to open a spreadsheet, the Rebrixe Time Difference Calculator works entirely in your browser: enter a start and end time, get the exact difference in hours and minutes instantly, overnight shifts included. No formulas, no account, no data sent anywhere.