Excel Time Difference: Hours, Minutes, Overnight
To calculate a time difference in Excel, subtract the start from the end: =B2-A2, formatted as h:mm. Multiply by 24 for decimal hours, by 1,440 for minutes or by 86,400 for seconds. If the end time is after midnight, use =MOD(B2-A2,1), which turns a 10:00 PM to 6:15 AM shift into 8:15 instead of a negative number. For totals over 24 hours, format the cell as [h]:mm. Excel stores a time as a fraction of a day, which is why those multipliers work. We tested every formula below in a spreadsheet engine and checked the answers in Python. This guide also covers converting between time zones in Excel (it has no time-zone functions) and the daylight-saving trap that makes night-shift totals wrong twice a year. Facts are as of November 2026.
Key facts (as of November 2026)
- Basic difference: =B2-A2. Microsoft: hours =INT((B2-A2)*24), minutes =(B2-A2)*1440, seconds =(B2-A2)*86400 (Microsoft Support).
- Overnight shifts: =MOD(B2-A2,1). MOD “has the same sign as divisor”, so the result is never negative (Microsoft Support).
- Over 24 hours: format as [h]:mm. Microsoft’s example: 12:45 + 15:30 shows 4:15 until formatted, then “28 hours and 15 minutes”.
- Time zones: add or subtract the offset as hours/24, e.g. =MOD(A2+5/24,1) for New York to London.
- On the night of March 14, 2027, 10:00 PM to 6:00 AM in New York is 7 real hours, not the 8 Excel reports; on November 7 it is 9 (our calculation, tzdata 2026f).
Why the multipliers work
Excel counts a day as 1, so an hour is 1/24, a minute 1/1,440 and a second 1/86,400. A difference of 8 hours 30 minutes is stored as 0.354166…, which our test sheet showed exactly. Formatting only changes the display; the value underneath is the fraction. Microsoft notes a limit of the time formats: “When you use the time format codes, hours never exceed 24, minutes never exceed 60, and seconds never exceed 60” (Microsoft, calculate the difference between two times). That is why totals need [h] and why the HOUR function, which Microsoft says “cannot exceed 24”, is wrong for long durations.
Formula cheat sheet
Results below are what our test sheet returned for two sample rows. Our time card calculator does the same sums without a spreadsheet.
| You want | Formula (start in A2, end in B2) | Result for 9:00 AM → 5:30 PM | Result for 10:00 PM → 6:15 AM |
|---|---|---|---|
| Elapsed time as a time | =B2-A2 (format h:mm) | 8:30 | negative (see below) |
| Elapsed time, overnight-safe | =MOD(B2-A2,1) | 8:30 | 8:15 |
| Decimal hours | =MOD(B2-A2,1)*24 | 8.5 | 8.25 |
| Total minutes | =MOD(B2-A2,1)*1440 | 510 | 495 |
| Total seconds | =MOD(B2-A2,1)*86400 | 30600 | 29700 |
| Whole hours only | =INT(MOD(B2-A2,1)*24) | 8 | 8 |
| As text | =TEXT(MOD(B2-A2,1),”h:mm”) | 8:30 | 8:15 |
| Overnight, IF version | =IF(B2<A2,B2+1,B2)-A2 | 8:30 | 8:15 |
Microsoft’s own hours formula wraps the multiplication in INT, which drops the minutes (“8” rather than “8.5”); use it when you want whole hours and leave INT off for decimal hours. The TEXT function displays a result in a given format and, as Microsoft notes, “the TEXT function takes precedence over the cell formatting”, but its result is text and cannot be summed.
Times that cross midnight
A plain =B2-A2 for 10:00 PM to 6:15 AM gives a negative time: in our test the engine showed −15:45. Microsoft says “Formulas that return dates and times as negative values can also show as #####” and advises keeping dates and times positive “when you’re using the 1900 date system”, Excel’s default (Microsoft, how to correct a ##### error). Two fixes:
- =MOD(B2-A2,1). Microsoft documents that “MOD(n, d) = n – d*INT(n/d)” and “The result has the same sign as divisor” (Microsoft, MOD). With a divisor of 1 (one day), −0.65625 becomes 0.34375, which is 8:15.
- =IF(B2<A2,B2+1,B2)-A2: adds a day to the end time when it is earlier than the start. Same answer, easier to read.
Both assume the shift is shorter than 24 hours. For longer spans, enter full dates and times (for example 3/13/2027 22:00 and 3/15/2027 06:00) and subtract them directly; the date part carries the extra days.
Totals over 24 hours
Adding a column of durations is where most timesheets break. Microsoft’s example: with 12:45 in one cell and 15:30 in another, =B2+B3 first shows “4:15”. To fix it, open Format Cells and “type [h]:mm;@”, after which “The result is 28 hours and 15 minutes” (Microsoft, add or subtract time). In our test, TEXT(…,”h:mm”) gave 4:15 and TEXT(…,”[h]:mm”) gave 28:15. For pay, convert to decimal hours with ×24 before multiplying by an hourly rate.
Converting between time zones in Excel
Excel has no built-in time-zone database, so you add the offset yourself as a fraction of a day and wrap it in MOD so the result stays a clock time. The offset depends on the date: both places may change clocks on different days. We computed the January and July 2027 offsets with Python zoneinfo (tzdata 2026f, re-read with glibc localtime()):
| From → to | January 2027 | Formula (time in A2) | July 2027 | Formula (time in A2) |
|---|---|---|---|---|
| New York → London | +5 h | =MOD(A2+5/24,1) | +5 h | =MOD(A2+5/24,1) |
| New York → Los Angeles | -3 h | =MOD(A2-3/24,1) | -3 h | =MOD(A2-3/24,1) |
| London → Mumbai | +5.5 h | =MOD(A2+5.5/24,1) | +4.5 h | =MOD(A2+4.5/24,1) |
| Los Angeles → Tokyo | +17 h | =MOD(A2+17/24,1) | +16 h | =MOD(A2+16/24,1) |
| Vancouver → Toronto | +2 h | =MOD(A2+2/24,1) | +3 h | =MOD(A2+3/24,1) |
| Sydney → London | -11 h | =MOD(A2-11/24,1) | -9 h | =MOD(A2-9/24,1) |
Notice the rows that change: London–Mumbai moves from 5.5 to 4.5 hours because the UK has summer time and India does not; Vancouver–Toronto from 2 to 3 hours because British Columbia stopped changing clocks on November 1, 2026; Sydney–London from 11 to 9 hours because the two hemispheres’ summers are opposite. New York–London is 5 hours in both months, but not in late March or early November, when the US and UK change on different dates. For those weeks, and for any pair, check our time zone converter or meeting planner rather than a fixed formula; the clock changes list gives every country’s dates.
The daylight saving trap
A spreadsheet treats every night as 24 hours of clock time. Real nights are not always that long. We measured two 2027 night shifts in New York as elapsed real time between local clock readings:
| Shift (New York) | Excel =MOD(B2-A2,1)*24 | Real elapsed hours |
|---|---|---|
| Sat Mar 13, 10:00 PM → Sun Mar 14, 6:00 AM | 8 | 7 (clocks spring forward) |
| Sat Nov 6, 10:00 PM → Sun Nov 7, 6:00 AM | 8 | 9 (clocks fall back) |
For hours-worked records this matters: on the November night the worker is on site for 9 hours. Payroll rules on how to count it differ by employer and jurisdiction; check yours. In a spreadsheet, the simplest fix is a manual adjustment column for the two change nights each year, which in the US in 2027 are March 14 and November 7 (15 U.S.C. §260a). Places that no longer change clocks, such as British Columbia, have no such nights. For storing exact instants, use UTC or Unix time; see our epoch time in Excel guide.
Google Sheets and LibreOffice
LibreOffice Calc gave the same results as the Excel documentation for every formula here; we ran our tests in LibreOffice Calc 24.2 (opening a file built with Excel formulas) and verified each answer in Python. Google Sheets uses the same function names, but we did not test it. Display of negative times differs between programs, so use MOD or IF rather than relying on how a negative result looks.
How we checked
We wrote every formula into an .xlsx file with openpyxl, recalculated it in LibreOffice Calc 24.2.7 (headless), exported the results and asserted them in Python, where we also recomputed each value with integer minutes. We did not run Microsoft Excel; Excel-specific behaviour (##### for negative times, the [h]:mm example) is quoted from Microsoft’s pages. Time-zone offsets and the 2027 night lengths come from Python zoneinfo on tzdata 2026f, re-read with glibc localtime(). Sources read on November 9, 2026.
Sources
- Microsoft Support, calculate the difference between two times
- Microsoft Support, add or subtract time in Excel
- Microsoft Support, MOD function
- Microsoft Support, how to correct a ##### error
- 15 U.S.C. §260a, daylight saving time dates (Cornell LII)
- IANA time zone database (version 2026f)
Frequently asked questions
How do I calculate the time difference between two times in Excel?
Use =B2-A2 and format the cell as h:mm. If the end can be after midnight, use =MOD(B2-A2,1).
How do I get the difference in hours as a decimal?
Multiply by 24: =MOD(B2-A2,1)*24. 9:00 AM to 5:30 PM gives 8.5.
Why does Excel show #### for a time difference?
Usually because the result is negative (the end time is earlier than the start). Use MOD or add a day to the end time.
How do I show more than 24 hours in Excel?
Format the cell with the custom format [h]:mm.
How do I calculate minutes between two times?
=MOD(B2-A2,1)*1440. 8:45 AM to 12:10 PM gives 205 minutes.
Can Excel convert time zones?
Not by itself. Add or subtract the offset as hours divided by 24, and check whether either place is on daylight saving time on that date.