Why does =B2-A2 return 0.375 instead of 9:00? Why does SUM() add up time but show 25:00 as 1:00? Why does formatting as [h]:mm not stick after copying?
The answer isn’t ‘you’re bad at Excel.’ It’s that Excel doesn’t store time the way you think it does—and no one tells you what’s really happening under the hood.
The Myth
Most people believe time in Excel is stored like clock faces: 9:00 AM = '9:00', 14:30 = '2:30 PM'. So they type =NOW()-A1, format the result as h:mm, and call it a day.
They assume Excel ‘knows’ it’s time. They assume adding 8 hours means typing +8. They assume TIME(14,30,0) is safer than arithmetic.
It’s not. All three assumptions break down the moment you cross midnight, sum more than 24 hours, or compare timestamps from different dates.
The Reality
Excel stores time as decimal fractions of a day. 1.0 = 1 full day = 24 hours. So 0.5 = 12:00 PM. 0.25 = 6:00 AM. 0.041666667 = 1:00 AM.
This isn’t theoretical—it’s baked into every calculation. And it explains why =B2-A2 returns 0.375 (9 hours = 9/24 = 0.375) and why =SUM(C2:C6) shows 1:00 when total hours exceed 24 (because 25 hours = 1.041666… → Excel displays only the fractional part unless you force [h]:mm).
| Criterion | ‘Type Time Like Text’ Approach | ‘Treat Time as Decimal’ Approach | Excel’s Actual Behavior |
|---|---|---|---|
| Storage of 15:00 | Text string “15:00” — no math possible | 0.625 (15 ÷ 24) | 0.625 — verified via =CELL("format",A1) & =VALUE(A1) |
| Add 3 hours to 22:00 | Fails — can’t add to text | =0.9166667 + 3/24 = 1.0416667 → 01:00 next day | Exactly matches: 22:00 + 3h = 01:00 → value = 1.0416667 |
| Sum of 8:00, 9:30, 10:45 | #VALUE! error | 0.3333 + 0.3958 + 0.4514 = 1.1805 → 28:20 formatted as [h]:mm | 1.1805555 = 28h 20m — confirmed by =TEXT(SUM(B2:B4),"[h]:mm") |
| Midnight boundary (23:00 → +2h) | Manual IF logic required | =0.9583 + 2/24 = 1.0416 → auto-corrects to next day | Yes — Excel handles date rollover natively if cell contains true datetime (not text) |
Why the Myth Persists
Because Excel’s UI hides the truth. When you type 14:30 into A1, Excel *displays* 2:30 PM—but stores 0.604166667. Right-click → Format Cells → Number tab → General shows it instantly.
YouTube tutorials from 2012 still say “just use TIME()” — ignoring that TIME(25,0,0) returns #NUM! (max hour = 23), while 25/24 works fine.
Older versions didn’t support [h]:mm well. Users learned workarounds—like splitting date/time into separate columns—and never unlearned them.
And Microsoft’s own help docs say “time values are stored as decimals,” then bury it under 17 subheadings. No one reads that far.
The Right Way
Do this — in order:
- Enter time as true time values. Type
14:30(not “2:30 PM”), or use=TIME(14,30,0). Never type quotes or apostrophes. - Use decimal math—not clock math. To add 7.5 hours:
=A1 + 7.5/24. To subtract 45 minutes:=A1 - 45/1440. - Format for display only — never for calculation. Apply [h]:mm only after math is done. Use Ctrl+1 → Custom → type
[h]:mm. - Verify storage with VALUE(). In D1, enter
=VALUE(C1). If it returns #VALUE!, C1 contains text—not time.
Here’s real sample data (A1:E8):
| Employee | Start | End | Break (min) | Hours Worked |
|---|---|---|---|---|
| Sarah Chen | 08:15 | 17:45 | 45 | =C2-B2-D2/1440 |
| James Wu | 13:00 | 02:20 | 30 | =IF(C3 |
| Maya Patel | 20:00 | 06:15 | 60 | =IF(C4 |
| David Kim | 09:30 | 18:00 | 30 | =C5-B5-D5/1440 |
| Lena Torres | 11:45 | 23:10 | 25 | =C6-B6-D6/1440 |
| Total Hours | =SUM(E2:E6) | |||
Now select E2:E6 → Ctrl+1 → Custom → [h]:mm. Total in E7 will show 84:20, not 12:20.
Keyboard shortcut you need: Alt+H+M+I opens Format Cells → Number tab instantly. No mouse needed.
Surprising tip: You don’t need TIME() for basic entry. Typing 18:30 auto-converts to 0.7708333. But if you paste time from email or web, it often arrives as text. Fix it with =--A1 (double-unary) — converts text “14:30” to number 0.597222.
Proof It Works
Below: same raw data, two approaches, same result — but only one survives copy-paste, sorting, and filtering.
| Task | ‘Text-Based’ Method (Myth) | ‘Decimal-Based’ Method (Reality) |
|---|---|---|
| Input “08:00” into A1 | Typed as "08:00" → cell shows “08:00”, VALUE(A1)=#VALUE! | Typed as 08:00 → VALUE(A1)=0.333333 |
| Add 12 hours | =A1+"12:00" → #VALUE! | =A1+12/24 → 0.833333 = 20:00 |
| Sort column A ascending | “01:00”, “10:00”, “2:00” — alphabetical, not chronological | 0.041666, 0.125, 0.416666 — sorts correctly |
| Filter for >15:00 | Fails — text filter sees “15:00” as string, not time | Works — numeric filter catches all values > 0.625 |
| Copy to new sheet | Paste as text — formulas break | Preserves decimal values — formulas stay intact |
Exceptions
There are exactly two cases where treating time as text *is* correct — and trying to convert breaks things.
- Duration-only fields without date context. If your sheet tracks “Shift Length” as “8h 30m” for reporting (not calculation), keep it as text. Converting to decimal adds zero value — and invites accidental math.
- Legacy systems exporting time as ISO strings. Some HR APIs send
"PT8H30M". Don’t force VALUE(). Use SUBSTITUTE + MID + VALUE only on the numeric parts. Or better: import as Power Query, parse duration withDuration.FromText().
If you’re tracking elapsed time across days (e.g., project timelines), always use full datetime: 2024-03-15 09:00 in A1, 2024-03-17 14:22 in B1. Then =B1-A1 gives 2.22361 → format as [d] \d [h]:mm to see “2 d 5:22”.
Your next step: Open your current time sheet. In an empty column, enter =VALUE(A1) beside your first time cell. If it returns #VALUE!, your entire sheet is built on the myth. Fix it now with =--A1, then drag down. Reapply [h]:mm formatting. Done.
