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. 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 “excel formula to calculate hours worked”. Start on a copy or a tiny sample, keep the affected formula visible, and compare the result with the outcome you expected 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. 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. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. For “excel formula to calculate hours worked”, 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 “calculate hours worked excel formula”, “how to calculate hours worked in excel using 24-hour clock”, “how to calculate working hours in excel” and “calculate hours worked excel”, 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. “calculate hours worked excel formula”, “how to calculate hours worked in excel using 24-hour clock”, “how to calculate working hours in excel” all point at the one operation explained on this page, which is why they all lead here.