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
Enter both times as real times (09:15, not the text "9.15") — right-aligned means Excel accepted them.
Subtract: =End - Start.
Format the result cell as [h]:mm via Ctrl+1 ▸ Custom, so totals past 24 hours keep counting.
For decimal hours, multiply by 24 and format as a plain number: =(End-Start)*24.
For overnight shifts use =MOD(End-Start, 1).
If the two moments are on different days, put date and time in one cell and subtract those instead.
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. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. Treat “excel hours between two times” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. 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. That turns a workflow that saves repeating the same clicks every week into a method you can reuse, explain, and defend when the workbook leaves your screen.
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. Check the referenced cells, argument order and result type in your workbook. Start with the worked example before changing an input, then compare the changed result with the calculation you expected. An error value or a result of a different type is a reason to inspect the inputs and arguments.
In Google Sheets
Google Sheets support for this task has not been verified. Check the function, arguments and workflow in the target product before transferring the example. Matching formula notation does not establish identical behaviour. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. For “excel hours between two times”, keep the input cells and the result visible while you check the formula in Excel. Follow the worked example and change one input at a time. The steps explain the workbook calculation; this page does not run a live calculator for that task.
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.