The Setup
Imagine you’re auditing shift logs for a logistics team in Shenzhen. You get a raw CSV dump with timestamps that look clean — but they’re inconsistent. Some are true time values; others are text masquerading as time. No one labeled the columns. You’ve got 9 rows of real data:| Employee | Shift Start | Shift End | Duration (hrs) |
|---|---|---|---|
| Li Wei | 8:15 AM | 4:45 PM | =C2-B2 |
| Amina Patel | 09:30:00 | 17:45:00 | =C3-B3 |
| Kenji Tanaka | 2024-03-15 06:00 | 2024-03-15 14:30 | =C4-B4 |
| Sarah Chen | 13:20 | 22:10 | =C5-B5 |
| Diego Morales | "7:00 AM" | "3:30 PM" | =C6-B6 |
| Fatima Al-Rashid | 0.291666667 | 0.739583333 | =C7-B7 |
| Rajiv Mehta | 2024/03/15 08:00 | 2024/03/15 16:15 | =C8-B8 |
| Yuki Sato | 12:00 AM | 11:59 PM | =C9-B9 |
| Miguel Torres | "14:00" | "23:00" | =C10-B10 |
The Challenge
You need to calculate accurate shift durations in decimal hours — not just display time. But column D gives #VALUE! in rows 6 and 10. Row 5 returns 0.375 — which is correct (9 hours), but you can’t tell by looking at it. And row 7? It subtracts full datetime values, so duration includes date deltas — even though both times are on the same day. The core issue isn’t formatting. It’s that Excel stores time as numbers, but only recognizes them as such when typed correctly or parsed unambiguously. Text strings with quotes, inconsistent separators, or mixed date-time formats break arithmetic. And here’s what most people miss: Excel has no native ‘time-only’ data type. All time is stored relative to 1 Jan 1900.Walking Through It
Start by diagnosing what’s *actually* in each cell. Select B2:B10. Press Alt+H+F+J (Home > Format > Format Cells > Number tab). Look at the Category. If it says “Text”, it’s not time — it’s text. If it says “Time” or “Custom”, it’s numeric — but verify with=ISNUMBER(B2). In our sample, B2 returns TRUE (it’s a real time), but B6 returns FALSE (it’s text).Step 1: Clean the quoted text entries. Select B6, B10, C6, C10. Press Ctrl+H. Find:
", Replace: (blank). Click Replace All. Now those cells still show quotes visually, but they’re gone from the formula bar.Step 2: Force conversion. In B11, enter
=TIMEVALUE(B6). Copy down to B15. Paste values over B6:B10. Do the same for C6:C10.Before cleanup:
| Cell | Raw Content | =ISNUMBER() | =CELL("format",B6) |
|---|---|---|---|
| B6 | "7:00 AM" | FALSE | G |
| B10 | "14:00" | FALSE | G |
| Cell | Value (as number) | Formatted as Time | =ISNUMBER() |
|---|---|---|---|
| B6 | 0.291666667 | 7:00 AM | TRUE |
| B10 | 0.583333333 | 2:00 PM | TRUE |
The Result
Now recalculate column D with=IF(C2>B2,C2-B2,1+C2-B2) to handle overnight shifts (e.g., Yuki Sato). Format D2:D10 as [h]:mm for elapsed time, then multiply by 24 to get decimal hours. Final cleaned table:| Employee | Shift Start | Shift End | Duration (hrs) |
|---|---|---|---|
| Li Wei | 8:15 AM | 4:45 PM | 8.5 |
| Amina Patel | 9:30 AM | 5:45 PM | 8.25 |
| Kenji Tanaka | 6:00 AM | 2:30 PM | 8.5 |
| Sarah Chen | 1:20 PM | 10:10 PM | 8.83 |
| Diego Morales | 7:00 AM | 3:30 PM | 8.5 |
| Fatima Al-Rashid | 7:00 AM | 5:55 PM | 10.92 |
| Rajiv Mehta | 8:00 AM | 4:15 PM | 8.25 |
| Yuki Sato | 12:00 AM | 11:59 PM | 23.98 |
| Miguel Torres | 2:00 PM | 11:00 PM | 9.0 |
What Could Go Wrong
Mistake #1: Using =NOW() inside TIMEVALUE(). TIMEVALUE ignores dates. So =TIMEVALUE(NOW()) returns only the time portion — but NOW() recalculates every second. That forces constant recalculation and breaks static time entry. Don’t do it.Mistake #2: Formatting a cell as Time *before* entering data. If you format A1 as h:mm and type “13:00”, Excel accepts it. But if you type “1300” (no colon), Excel stores it as 1300 — not 13:00. It becomes 1300 days after 1 Jan 1900. That’s 23 Mar 1903.
Mistake #3: Subtracting time across date boundaries without checking. If B2 = 2024-03-15 23:00 and C2 = 2024-03-16 06:00, C2–B2 = 0.291666667 (7 hours). Correct. But if someone enters C2 as “6:00” *without* the date, Excel assumes 1900-01-00 6:00 — so C2–B2 = -0.708333333. Negative time. Your IF() logic must catch that.
Quick reference:
| Operation | Excel Equivalent | Notes |
|---|---|---|
| 1 second | 1/86400 = 0.000011574 | 86400 seconds per day |
| 1 minute | 1/1440 = 0.000694444 | 1440 minutes per day |
| 1 hour | 1/24 = 0.041666667 | This is why 12:00 PM = 0.5 |
| Midnight (00:00) | 0 | Base value for time-only |
| 1 Jan 1900 | 1 | Date system starts here |
