To find the hours between a start and end time — shift length, time logged — subtract the two and multiply by 24. Excel stores time as a fraction of a day, so the ×24 turns it into hours.
The example
Shift start and end times, converted to hours worked.
| A | B | C | |
|---|---|---|---|
| 1 | Start | End | Hours |
| 2 | 9:00 AM | 5:30 PM | 8.5 |
| 3 | 8:15 AM | 12:00 PM | 3.75 |
The formula
Hours worked in C2:
How it works
Time is a fraction of a day under the hood:
- Excel stores 24 hours as 1.0, so 6:00 AM is 0.25, noon is 0.5, and so on.
- Subtracting two times gives the elapsed fraction of a day.
- Multiplying by
24converts that fraction to hours — 0.354 → 8.5. - Format the result cell as a number, not time, or you’ll see “8:30” instead of “8.5.”
Crossing midnight? A shift from 10 PM to 6 AM gives a negative result. Wrap with MOD: =MOD(B2 - A2, 1) * 24 handles the overnight wrap correctly.
Try it: interactive demo
Pick a start and end time; see the hours between.
Variations
Handle overnight shifts
MOD wraps past midnight:
Show as h:mm duration
Keep it as time and use a bracketed format:
Total minutes
Multiply by 1440 instead of 24:
Pitfalls & errors
Result shows as a time. The cell often inherits time formatting — format it as Number to see decimal hours.
Negative time across midnight. Plain subtraction breaks for overnight shifts. Use MOD(B2-A2, 1)*24.
Over 24 hours needs [h]. To display durations beyond a day, use the bracketed format [h]:mm so hours don’t roll over.
Practice workbook
Frequently asked questions
How do I calculate hours between two times in Excel?
How do I handle a shift that crosses midnight?
Why does my time difference show as a clock time?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class