How to Calculate Elapsed Time in Excel: A Complete, Practical Guide
Calculating elapsed time in Excel is one of those skills that looks trivial until your result reads ######, or a nine-hour shift somehow reports 33 hours, or the difference between 9:00 PM and 2:00 AM refuses to cooperate. Now, the good news is that once you understand how Excel actually stores time, every one of these problems becomes predictable and easy to fix. This guide walks through the core logic, seven practical formulas for real-world scenarios, and the troubleshooting steps that solve the errors most people hit Easy to understand, harder to ignore..
Why Elapsed Time in Excel Confuses Almost Everyone
The root cause of nearly every time-calculation problem is a mismatch between how Excel stores time and how Excel displays time. You type 14:30 and see a clock. When you subtract one time from another, Excel performs ordinary arithmetic on those hidden numbers, then formats the answer using whatever format the cell happens to carry. Which means excel stores a decimal number. If that format is wrong, the answer looks wrong even when the math is perfect It's one of those things that adds up..
Understanding the storage layer removes about 90% of the guesswork Not complicated — just consistent..
The Golden Rule: Excel Sees Time as a Fraction of a Day
Excel dates and times live in a single serial number system:
- 1 equals January 1, 1900.
- 1.5 equals January 1, 1900 at 12:00 PM (noon).
- .25 equals 6:00 AM on the fictional day zero.
Because a full day equals 1, every unit of time is simply a fraction:
| Unit | Decimal value |
|---|---|
| 1 hour | 1/24 ≈ 0.0416667 |
| 1 minute | 1/1440 ≈ 0.0006944 |
| 1 second | 1/86400 ≈ 0. |
This single fact explains three things at once: why subtracting times works with a plain minus sign, why multiplying by 24 converts hours to decimals, and why formatting matters so much Small thing, real impact. No workaround needed..
Method 1: Basic Elapsed Time Between Two Clock Times
Suppose A2 holds a start time and B2 holds an end time.
=B2-A2
That is genuinely all the math required. The critical part is the cell format. Select the result cell, press Ctrl+1, and choose a custom format:
h:mm→ displays 7:30 for seven and a half hours[h]:mm→ displays 31:30 for thirty-one and a half hours
The square brackets are the whole trick. Without them, Excel wraps the hour count at 24, so a 31-hour total silently becomes 7:00. **Any time you are summing or subtracting durations that could exceed 24 hours, use [h]:mm:ss.
Method 2: When the Shift Crosses Midnight
Subtract 9:00 PM from 2:00 AM and Excel returns a negative number, which it cannot display as a time. The MOD function solves this elegantly
