Yes, Excel displays AM/PM correctly in time cells. But if your =HOUR(A1) returns 13 for "1:00 PM", you’ve already lost the battle — because Excel stores times as decimals, not strings.
Quick Answer
Use =TEXT(A1,"h:mm AM/PM") to display AM/PM cleanly, or =TIME(HOUR(A1),MINUTE(A1),SECOND(A1)) to rebuild a true time value from text — but never rely on formatting alone when calculating across time zones or shifts.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| TEXT + Custom Format | Apply =TEXT(A1,"h:mm AM/PM") or format cell as "h:mm AM/PM" | Display-only reports, dashboards, labels | Output is text — can’t be summed or used in TIMEVALUE() |
| TIMEVALUE() + TEXT | =TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A1,"a","A"),"p","P")) | Converting imported text like "7:45 pm" or "3:20am" | Fails on inconsistent spacing or missing spaces before am/pm |
| IF + HOUR/MINUTE logic | =IF(HOUR(A1)>12,HOUR(A1)-12,HOUR(A1))&":"&TEXT(MINUTE(A1),"00")&" "&IF(HOUR(A1)>=12,"PM","AM") | Legacy systems that reject TIMEVALUE(), or when debugging | Breaks at midnight (0:00 → 12:00 AM) unless adjusted manually |
| Custom Number Format Only | Right-click → Format Cells → Custom → enter "h:mm AM/PM" | Preserving numeric time values while changing appearance | Does nothing if original cell contains text — only works on real time serial numbers |
| Power Query Clean + Time.From | Transform column → Replace "am"/"pm" → Split on space → Combine → Time.From | Bulk cleaning messy HR shift logs or CSV imports | Overkill for 10 rows; requires PQ knowledge |
Method 1 Deep Dive
The beauty of =TEXT(A1,"h:mm AM/PM") is its simplicity — but only if A1 holds a real Excel time value. Try it on this sample:
| A1 | B1 (Formula) | Result |
|---|---|---|
| 0.375 | =TEXT(A1,"h:mm AM/PM") | 9:00 AM |
| 0.645833333 | =TEXT(A1,"h:mm AM/PM") | 3:30 PM |
| 0.041666667 | =TEXT(A1,"h:mm AM/PM") | 1:00 AM |
| 0.979166667 | =TEXT(A1,"h:mm AM/PM") | 11:30 PM |
| 0.5 | =TEXT(A1,"h:mm AM/PM") | 12:00 PM |
Notice how 0.5 = noon — not 12:00 AM. That’s Excel’s internal time serial number (1 = 24 hours, so 0.5 = 12 hours). What makes this elegant is that no arithmetic is needed: TEXT does all the heavy lifting. But here’s the counterintuitive tip: if you copy-paste that TEXT result elsewhere, it becomes static text — so never use it in downstream calculations like =B1+TIME(1,0,0).
Method 2 Deep Dive
When your source data looks like "8:15 pm" or "11:02am" in column A (as text, not time), TIMEVALUE() is your lifeline — but only after cleanup. Excel’s TIMEVALUE() is case-sensitive and demands exact spacing. So first, standardize:
In B1, enter:=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(UPPER(TRIM(A1)),"AM"," AM"),"PM"," PM"))
This does four things: trims whitespace, forces uppercase, inserts a space before AM/PM, then converts. Test it on real data:
| A1 | B1 (Formula) | C1 (Formatted as h:mm AM/PM) |
|---|---|---|
| 7:45 pm | =TIMEVALUE(...) | 7:45 PM |
| 11:02am | =TIMEVALUE(...) | 11:02 AM |
| 3:00 p.m. | =TIMEVALUE(...) | #VALUE! (needs extra SUBSTITUTE for "p.m.") |
| 12:00 midnight | =TIMEVALUE(...) | #VALUE! (use "12:00 AM" instead) |
| 1:15 a.m. | =TIMEVALUE(...) | 1:15 AM |
Pro tip: Press Alt+H+N+F to open Format Cells > Number tab quickly — then type "h:mm AM/PM" into the Type box and hit Enter. You’ll see the time snap into clean display instantly. And if your data has mixed cases like "PM" and "p.m.", add one more SUBSTITUTE: SUBSTITUTE(SUBSTITUTE(...,"P.M.","PM"),"A.M.","AM").
Cheat Sheet
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Check if cell contains real time: =ISNUMBER(A1) | TRUE = safe for TIMEVALUE(); FALSE = text needs cleaning | Ctrl+` (to toggle formula view) |
| 2 | Standardize am/pm casing & spacing in B1 | "8:30 pm" → "8:30 PM" | Alt+H+F+F → Format Cells |
| 3 | Convert cleaned text to time: =TIMEVALUE(B1) | 0.354166667 (Excel’s serial number for 8:30 AM) | F2 → Enter (to recalc after edit) |
| 4 | Display cleanly: Format C1 as "h:mm AM/PM" | 8:30 AM | Alt+H+N+F → Type "h:mm AM/PM" → Enter |
| 5 | Validate across range: =COUNT(C1:C10)/COUNTA(C1:C10) | Should be 1.0 — any lower means some cells failed conversion | Ctrl+Shift+U (to underline headers) |
| 6 | Fix midnight edge case: =IF(A1="12:00 am",0,TIMEVALUE(A1)) | Ensures 12:00 AM = 0.0, not #VALUE! | Alt+= (AutoSum — then edit formula) |
