Calculate Time Difference in Excel (Hours, Minutes, Overnight)
Calculate time differences in Excel: hours worked, decimal hours for pay, overnight shifts, totals over 24 hours, and the [h]:mm format that fixes odd results.
By Ayoub Farid · · 1 min read
Timesheets, shift lengths and task durations all need time maths. Excel handles it well once you know that times are fractions of a day.
How Excel stores time
One day = 1. So 12:00 = 0.5 and 06:00 = 0.25. That's why subtracting times works, and why multiplying by 24 gives hours.
Hours worked
Start in B2 (09:00), end in C2 (17:30):
=C2-B2 → 8:30Format the result as h:mm.
Decimal hours (for pay)
=(C2-B2)*24 → 8.5Format as Number. Pay: =(C2-B2)*24*$F$1, with the hourly rate locked in F1.
Minus a break
=(C2-B2-D2)*24where D2 holds the break (00:30).
Overnight shifts
22:00 to 06:00 gives a negative result. Use MOD:
=MOD(C2-B2,1)→ 8:00.
Totals over 24 hours
Summing a week of shifts might show 16:00 instead of 40:00, because h:mm wraps at 24 hours. Format the total as [h]:mm (Format Cells > Custom). The square brackets show total hours.
Minutes or seconds
=(C2-B2)*1440 minutes
=(C2-B2)*86400 secondsDates and times together
If cells hold date and time ("02/11/2026 22:00"), plain subtraction works across days without MOD. See TODAY and NOW.
Frequently asked questions
How do I calculate hours between two times in Excel?
=(end-start)*24 for decimal hours, or =end-start formatted as h:mm.
How do I calculate time across midnight?
=MOD(end-start,1).
Why does my total time show less than 24 hours?
Use the [h]:mm format so hours don't wrap at 24.
Why do I get ##### in a time cell?
The result is negative (e.g. an overnight shift without MOD) or the column is too narrow.