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. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. Treat “excel calculate 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 calculation you can defend to a CFO or an auditor 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. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. For “excel calculate 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.
Other ways people ask this
People reach this page typing “excel calculate hours between 2 times”, “excel formula to calculate hours between two times”, “how to calculate hours between two times in excel” and “excel calculating hours between two times”, among other phrasings; whichever wording you used, the fix above is the one you want.
Why do people search for this in so many different ways?
Because the same task has many names. “excel calculate hours between 2 times”, “excel formula to calculate hours between two times”, “how to calculate hours between two times in excel” all point at the one operation explained on this page, which is why they all lead here.