Excel Formula to Calculate Hours Worked

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 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. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.

In Google Sheets

Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. 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, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Treat “excel formula to calculate hours worked” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.

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