In Excel: subtract the earlier time from the later one and format the result as [h]:mm — or multiply by 24 to get decimal hours you can pay against.
On this page7
8.00 decimal hours
Need it as an auditable file?
Ships inside the linked template — formula-driven, unlocked, audit-ready.
What this does
Excel stores a time as a fraction of a day: 06:00 is 0.25, 12:00 is 0.5. Subtracting two times therefore gives a fraction of a day, and the formatting decides what you see. The square brackets in [h]:mm are the critical part — plain h:mm resets to zero after 24 hours, so a 30-hour total displays as 6:00, which is the classic wrong timesheet. Multiplying the difference by 24 converts it to decimal hours (7.5 rather than 7:30), which is what payroll arithmetic needs. A shift crossing midnight makes the subtraction negative; =MOD(End-Start, 1) handles it by wrapping into the next day. And where the two moments fall on different DATES, store date and time together in one cell — subtracting a bare 09:00 on Tuesday from a bare 17:00 on Monday cannot possibly know a day passed. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “calculate time difference in excel”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the tool above before you touch the real workbook. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. The point is a calculation you can defend to a CFO or an auditor, but the practical win is that someone else can open the file and understand what happened without asking you.
A worked example
Start 09:15 in B2, end 17:45 in C2. =C2-B2 formatted as [h]:mm shows 8:30. =(C2-B2)*24 formatted as a number shows 8.5 — the figure to multiply by an hourly rate. A night shift with start 22:00 and end 06:00: =C2-B2 shows ###### or a negative; =MOD(C2-B2, 1) shows 8:00. Timesheet arithmetic fails in exactly two ways — the 24-hour wrap and the midnight crossing — and both are one formatting choice and one MOD away from being right. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.
In Google Sheets
If you are in Google Sheets rather than Excel, the good news is that the formula shown here is identical and the workflow barely changes — menus sit across the top instead of in a ribbon, and a few function names differ slightly, but anything you build here moves across with little or no rework. Keep this page bookmarked for the next time the same question comes up. Better still, rebuild the example once in your own sheet — doing it yourself, with the tool above to check against, is what turns a copied formula into a technique you own. If you take one thing from this page on “calculate time difference in excel”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.
Common mistakes
- Formatting a total as h:mm rather than [h]:mm, so anything over 24 hours silently wraps.
- Subtracting bare times across midnight and getting ###### or a negative number.
- Paying against a time value directly — 8:30 as a number is 0.354, so it must be multiplied by 24 first.
- Entering times as text, which right-alignment (or the lack of it) reveals immediately.
- Summing a column of daily durations without the bracket format, which is where the wrong weekly total comes from.
Frequently asked questions
How do I calculate the difference between two times in Excel?
Subtract the earlier from the later — =End-Start — and format the cell as [h]:mm.
How do I get the number of hours as a decimal?
Multiply the difference by 24: =(End-Start)*24, formatted as a plain number. 7:30 becomes 7.5.
Why does my time difference show ###### or a negative?
The shift crosses midnight, so End is numerically smaller than Start. Use =MOD(End-Start, 1).
Why does my total reset after 24 hours?
The cell is formatted h:mm. Change it to [h]:mm — the brackets are what let the hours run past 24.
Other ways people ask this
On the way here you may have searched this as “excel calculate time difference”, “how to calculate difference in time in excel”, “calculate difference of time in excel” and “how can i calculate time difference in excel” — it is all the same task, and this page is the single, complete answer to it.
Why do people search for this in so many different ways?
Because the same task has many names. “excel calculate time difference”, “how to calculate difference in time in excel”, “calculate difference of time in excel” all point at the one operation explained on this page, which is why they all lead here.