Convert Time to Decimal Hours in Excel — The ×24 Trick (and Calculate Hours Worked)

TL;DR — Excel stores a time as a fraction of a day, so 8:15 is the number 0.34375, not 8.25. To get decimal hours you multiply by 24: =A2 * 24 turns 8:15 into 8.25. Hours worked between two times is =(end - start) * 24. This is why hours * rate comes out 24× too small until you add the * 24. For a night shift that crosses midnight, wrap the subtraction in MOD so the negative fraction rolls forward: =MOD(end - start, 1) * 24.

=A2 * 24                        ' 8:15  -> 8.25   (decimal hours)
=(B2 - A2) * 24                 ' 09:00 to 17:30 -> 8.5 hours
=(B2 - A2) * 24 * 20            ' 8.5 hours at $20 -> 170  (money)
=MOD(B2 - A2, 1) * 24           ' 22:00 to 06:00 -> 8      (crosses midnight)
=(B2 - A2 - C2) * 24            ' minus a 0:30 unpaid break -> 8.0

Payroll, timesheets, billable hours, machine run-times — sooner or later you need a time as a number you can multiply, not a clock reading. The problem is that Excel's clock value looks like 8:15 but is 0.34375, so every "why is my total wrong" question here traces back to forgetting to convert the fraction into hours. One multiplication fixes it. This guide covers that conversion, the pay-rate bug it solves, the midnight-crossing trick, and where rounding belongs.

What you'll learn

Why a time is a fraction, and how × 24 turns it into decimal hours
Calculating hours worked between two times
The hours × rate bug — and why the answer is exactly 24 times too small
The MOD trick for night shifts that cross midnight
Subtracting unpaid breaks, and rounding to the nearest quarter hour

The mental model: Excel counts in days, so multiply by 24

Excel's serial clock counts days. Midnight is 0.0, and each hour adds 1/24 of a day, so 6:00 is 0.25 and 8:15 is 8.25 / 24 = 0.34375. That is a perfectly good number for storing and adding times, but useless for paying someone — no one is owed 0.34375. Multiplying by 24 rescales the day-fraction into the unit you actually want:

=A2 * 24        ' fraction of a DAY  -> decimal HOURS   (0.34375 -> 8.25)
=A2 * 1440      ' -> total MINUTES   (24 * 60)
=A2 * 86400     ' -> total SECONDS   (24 * 60 * 60)

Hold that: the number is already correct; × 24 just changes the unit from days to hours. Everything below is this one idea applied to real timesheets.

Hours worked between two times

Subtract the start from the end — that gives the elapsed day-fraction — then multiply by 24:

' A2 = 09:00 (start), B2 = 17:30 (end)
=B2 - A2         ' -> 0.354167   (a fraction; format wants to show it as 8:30)
=(B2 - A2) * 24  ' -> 8.5        (decimal hours you can pay on)

Leave off the * 24 and Excel will happily format 0.354167 as 8:30, which looks right — until you try to do maths with it.

The pay-rate bug: exactly 24× too small

This is the number-one timesheet error. You have decimal-looking hours and a rate, so you multiply — and the paycheque is absurd:

' B2 - A2 is 0.354167 (which displays as 8:30)
=(B2 - A2) * 20        ' -> 7.083...   WRONG (looks like $7 for 8.5 hours)
=(B2 - A2) * 24 * 20   ' -> 170        RIGHT ($20 x 8.5 hours)

The wrong answer is 170 / 24 = 7.08 — off by a factor of exactly 24, every time, because you multiplied the day-fraction by the rate instead of the hours. The fix is never a special function; it's the missing * 24 that converts days to hours before the money maths.

Night shifts: the MOD trick for crossing midnight

Clock out earlier than you clocked in — 22:00 to 06:00 — and end - start goes negative (0.25 − 0.9167 = −0.6667), which displays as #######. The elegant fix is MOD(..., 1): taking the difference mod one day rolls the negative fraction forward into the correct positive span.

' A2 = 22:00 (start), B2 = 06:00 (end, next morning)
=(B2 - A2) * 24          ' -> -16     wrong, and shows ####### unformatted
=MOD(B2 - A2, 1) * 24    ' -> 8       correct: 8 hours overnight

MOD(diff, 1) works whether or not the shift crosses midnight, so it's the safe default for any elapsed-time column where the end can be "the next day". (If a shift can exceed 24 hours you need real start dates, not just times — store full timestamps.)

Breaks, and rounding billable time

Unpaid breaks are just another subtraction inside the parentheses, still in day-fractions until the final * 24:

' A2 start, B2 end, C2 = 0:30 unpaid break (the value 0.5 hours as a time)
=(B2 - A2 - C2) * 24     ' -> 8.0    net paid hours

Round after converting, on the decimal hours, so the rounding unit is intuitive:

=MROUND(A2 * 24, 0.25)   ' nearest quarter hour  (8.20 -> 8.25)
=CEILING(A2 * 24, 0.25)  ' always round billable time UP to a quarter hour

To go the other way — decimal hours back into a h:mm time — divide by 24 and format as time: =8.5 / 24 shown as time is 8:30.

The judgment call

Paying or charging for time → convert to decimal hours with * 24 before any multiplication. The rate maths must see 8.5, never 0.354.
A column where the end can be the next morning → MOD(end - start, 1) * 24 as the default; it's correct with or without a midnight crossing.
Totals you'll keep doing maths on → store decimal hours (a plain number), not a h:mm time; numbers don't wrap at 24 the way the time format does.
A human-readable elapsed total → keep it as time and use the [h]:mm format instead of converting — see summing time over 24 hours.
Rounding → round the decimal hours with MROUND/CEILING, not the raw fraction, so "nearest 0.25" means a quarter of an hour.

How ExcelMaster helps

The × 24 is invisible knowledge — nothing on screen tells you the friendly-looking 8:30 is really 0.354, so the pay-rate bug ships to production constantly. ExcelMaster recognises timesheet intent ("total the hours", "pay at this rate", "night shift") and writes the whole formula — the conversion, the MOD for overnight spans, the break subtraction, the right rounding — so the number that reaches your payroll is hours, not day-fractions. You state the shift; it does the arithmetic Excel hides.

Frequently asked questions

How do I convert hh:mm to decimal hours in Excel?

Multiply by 24: =A2 * 24. Because Excel stores a time as a fraction of a day, 8:15 is 0.34375, and × 24 rescales it to 8.25 decimal hours. Format the result cell as a plain number, not as time.

Why is my hours × rate calculation wrong?

You multiplied the day-fraction by the rate instead of the hours, so the answer is exactly 24 times too small. Use =(end - start) * 24 * rate. The * 24 converts the fraction to hours before the money maths.

How do I calculate hours for a night shift that crosses midnight?

Use =MOD(end - start, 1) * 24. MOD(..., 1) rolls the negative difference forward into the correct positive span, so 22:00 to 06:00 returns 8. It also works for normal daytime shifts, so it's the safe default.

How do I sum hours worked across a whole week?

Sum the decimal-hour column with =SUM(...), or sum the time column and format the total as [h]:mm so it doesn't reset at 24 hours — see summing time over 24 hours.

How do I round worked time to the nearest quarter hour?

Convert first, then round: =MROUND(A2 * 24, 0.25) snaps decimal hours to the nearest 0.25. Use CEILING(A2 * 24, 0.25) to always round billable time up.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-25.