It’s 3:18 PM on a Tuesday. You just pasted a list of meeting start times from Outlook into column A—‘10/3/2023 9:15 AM’, ‘10/4/2023 2:30 PM’—and typed =A2-A1 in B2. Instead of getting ‘1 day 5 hours 15 min’, you see 1.225694444. Your colleague says ‘just format it as [h]:mm’—but now B2 shows 5:25, not 29:15. You’re not crazy. Excel *is* doing math. It’s just doing it with numbers no one told you about.
Quick Answer
Excel stores every date as an integer counting days since January 1, 1900 (serial number 1), and every time as a decimal fraction of a day—so 12:00 PM is 0.5, 6:00 AM is 0.25, and 3:00 PM on October 3, 2023 is 45202.625. That’s why =TODAY() returns 45202, and =NOW() returns something like 45202.64273.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Serial Number Conversion | Enter =DATE(2023,10,3) → returns 45201. Format cell as General to see it. | Debugging date mismatches, auditing imported data | Not intuitive for non-technical users; requires knowing base date |
| Format Cells Dialog | Select cells → Ctrl+1 → Number tab → Category: Date/Time → pick format | Quick display fixes without changing underlying values | Doesn’t fix misinterpreted text-dates (e.g., ‘Oct-23’ as Oct 2023 vs Oct 1, 2023) |
| TEXT Function + Serial Math | =TEXT(A2,"yyyy-mm-dd") & " " & TEXT(A2,"h:mm AM/PM") — works even if A2 is pure number | Creating clean reports or exporting to email/SAP | Output is text—no longer usable in calculations |
| DATEVALUE + TIMEVALUE | =DATEVALUE("10/3/2023")+TIMEVALUE("9:15 AM") → returns 45201.385416667 | Cleaning inconsistent date-time strings from CSV imports | Fails on ambiguous formats (e.g., ‘01/02/03’) without system locale context |
Method 1 Deep Dive
Let’s say you get a CSV from your HRIS with column A containing raw timestamps like ‘2023-10-03 09:15:22’. Excel often imports these as text. Try this:
- Select A2:A10 (Sarah Chen, Acme Corp, $45,200 salary, hired 2023-10-03 09:15:22).
- Press Alt+H+F+J (Home → Fill → Justify). Yes—Justify. This forces Excel to reinterpret mixed text/numbers.
- If that fails, enter =DATEVALUE(LEFT(A2,10))+TIMEVALUE(RIGHT(A2,8)) in B2, then copy down.
Now check B2:B10 with Format Cells → General. You’ll see numbers like 45201.38565. That .38565? Multiply by 24 → 9.2556 hours → 9:15:22 AM. Here’s the surprise: Excel’s 1900 date system includes February 29, 1900—even though 1900 wasn’t a leap year. Microsoft kept the bug for Lotus 1-2-3 compatibility. So serial number 60 = Feb 29, 1900—a nonexistent date. Any formula using =DATE(1900,2,29) will return 2/29/1900, but =ISLEAPYEAR(1900) is FALSE. Don’t build century-old calendars.
Sample data in A1:B7:
| Raw Input | Converted Serial | Formatted as mm/dd/yyyy h:mm |
|---|---|---|
| 2023-10-03 09:15:22 | 45201.38565 | 10/03/2023 9:15 AM |
| 2023-10-04 14:30:00 | 45202.60417 | 10/04/2023 2:30 PM |
| 2023-10-05 18:45:11 | 45203.78137 | 10/05/2023 6:45 PM |
| 2023-10-06 00:00:00 | 45204.00000 | 10/06/2023 12:00 AM |
| 2023-10-07 23:59:59 | 45205.99999 | 10/07/2023 11:59 PM |
Method 2 Deep Dive
Ever tried subtracting two times and gotten negative time? Here’s why—and how to fix it.
In C1:C5, enter these actual shift start/end times:
- C1: 07:00 AM (format as Time → Excel stores as 0.291666667)
- C2: 03:30 PM (0.645833333)
- C3: 11:00 PM (0.958333333)
- C4: 06:15 AM next day (0.260416667—but wait, that’s *earlier*, so Excel treats it as same day unless you add 1)
To calculate overnight shift duration properly, use =IF(C4<C3,C4+1-C3,C4-C3) in D4. That +1 adds a full day (1.0) to handle wraparound. Now D4 returns 0.4375 → 10.5 hours → 10:30.
This matters in real payroll files. At “Sunrise Logistics”, their night-shift log had 1,247 rows where end time was earlier than start. Without the +1 logic, they underpaid 83 staff by 11–14 minutes per shift over Q3.
Try this shortcut: select any time cell → press Ctrl+Shift+2 to apply h:mm AM/PM instantly. No mouse needed.
Cheat Sheet
| Task | Formula / Shortcut | Cell Example | Notes |
|---|---|---|---|
| See underlying serial number | Format Cells → General | A1 = 10/3/2023 → shows 45201 | Always do this first when debugging |
| Convert text date to real date | =DATEVALUE(A2) | A2 = "Oct 3, 2023" → 45201 | Fails on “3-Oct-23” unless regional settings match |
| Add 5 days to a date | =A2+5 | A2 = 45201 → 45206 = Oct 8, 2023 | No DATEADD() function—just arithmetic |
| Extract hour from time | =HOUR(A2) | A2 = 14:30 → returns 14 | Works on serials too: HOUR(0.60417) = 14 |
| Show elapsed time >24 hrs | Custom format: [h]:mm | B2-B1 = 1.22569 → displays as 29:25 | Brackets [] force overflow handling |
