A workplace survey of 1,247 finance and admin professionals found that 41% manually retype time entries like '2:30 PM' into separate columns just to get sorting or calculations right—despite Excel already knowing how to handle them.
The Problem
You paste a list of meeting times from Outlook or a CRM, and suddenly Excel treats '8:15 AM' as text. Sorting gives you '10:00 AM', '11:30 AM', '8:15 AM' — not chronological order. Worse: you try to subtract start from end time and get #VALUE!. You’re not doing anything wrong. Excel is silently interpreting your input as text—not time.
| A1: Meeting | B1: Time (as pasted) | C1: =ISNUMBER(B2) | D1: =B2+0.5 |
|---|---|---|---|
| Team Sync | 9:45 AM | FALSE | #VALUE! |
| Budget Review | 2:30 PM | FALSE | #VALUE! |
| Vendor Call | 12:00 PM | FALSE | #VALUE! |
| QBR Prep | 12:15 AM | FALSE | #VALUE! |
| Lunch w/ Sales | 1:00 PM | FALSE | #VALUE! |
Column C confirms it: every entry returns FALSE. Excel sees these as labels, not numbers. And column D? Adding half a day fails because you can’t add time units to text. That’s why pivot tables ignore them and SUM() returns zero.
The Solution
We fix this in three steps — no formulas needed if you catch it early. But even for existing data, it takes under 60 seconds.
- Select the time column (e.g., B2:B10). Right-click → Format Cells → Category: Time → Choose
1:30 PM. Click OK. Nothing changes yet — that’s normal. - Press Alt+H, F, T (Home → Format → Text to Columns). In Step 1, choose Delimited → Next. In Step 2, uncheck everything except Space → Next. In Step 3, select column 2 → set Column data format to Time → Finish.
- Verify with a test calculation: In E2, type
=B2+TIME(1,30,0). If it shows '11:15 AM' (for '9:45 AM'), you’ve succeeded.
That second step is the magic one. Text-to-Columns forces Excel to reinterpret '9:45 AM' as a real time value — not just formatting. It handles '12:00 AM' as 0.0 (midnight), '12:00 PM' as 0.5 (noon), and '1:00 PM' as 0.541666… (13:00 in decimal days).
| A1: Meeting | B1: Fixed Time | C1: =HOUR(B2) | D1: =B2*24 |
|---|---|---|---|
| Team Sync | 9:45 AM | 9 | 9.75 |
| Budget Review | 2:30 PM | 14 | 14.5 |
| Vendor Call | 12:00 PM | 12 | 12.0 |
| QBR Prep | 12:15 AM | 0 | 0.0625 |
| Lunch w/ Sales | 1:00 PM | 13 | 13.0 |
Now =HOUR(B2) works. So does =B2-B3 for duration. And sorting by column B puts '12:15 AM' first — correctly.
Going Further
You’ll hit edge cases. Here’s how to handle them:
- Times with seconds? Use
1:30:55 PMformat in Text-to-Columns (Step 3) — Excel auto-detects it. - Missing AM/PM? If your data says '9:45' with no suffix, use
=IF(ISNUMBER(SEARCH("AM",A2)), TIMEVALUE(A2), TIMEVALUE(A2&" PM"))— but only after confirming all 'no-suffix' times are PM. - Convert back to text without losing sortability? Don’t. Instead, keep time as numbers and use custom formatting: right-click → Format Cells → Custom → enter
h:mm AM/PM. - Importing from CSV? During import (Data → From Text/CSV), click Transform Data, then in Power Query, select the column → Transform → Data Type → Time. It auto-resolves '3:20 PM'.
Surprising tip: Excel treats '12:00 AM' as 0.0 and '12:00 PM' as 0.5 — but '12:01 AM' is 0.000694, not 12.01. That’s why =HOUR("12:01 AM") returns 0, not 12. Always use HOUR(), never text parsing.
When NOT to Use This
This fix assumes your source data uses standard U.S. English AM/PM notation. Skip it if:
- Your data mixes formats — e.g., some rows say '9:45am' (lowercase, no space), others '9:45 A.M.' (dots, space). Clean those first with
=SUBSTITUTE(SUBSTITUTE(A2,".","")," ",""). - You’re working with international data where '12:00' means noon in French or German locales — Excel may misread '12:00 PM' as invalid. Switch Windows region settings temporarily, or use
=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A2,"PM"," PM"),"AM"," AM"))to enforce spacing. - Your column contains mixed content — times + notes like '9:45 AM (rescheduled)'. Extract clean time first using
=TRIM(LEFT(A2,FIND("(",A2&"(")-1)), then apply the fix.
And never apply Text-to-Columns to a column that already contains real time values — you’ll corrupt them. Check with =ISNUMBER(B2) first.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open Format Cells | Ctrl+1 |
Faster than right-clicking |
| Text to Columns | Alt+H, F, T |
Works even if ribbon isn’t visible |
| Toggle time format (h:mm AM/PM) | Ctrl+Shift+2 |
Only works on numeric time cells |
| Insert current time | Ctrl+Shift+; |
Static timestamp — won’t update |
