What Most People Miss About How to Format Time in Excel

A workplace survey of 1,200 mid-level analysts found that 58% manually retype time values just to make them readable — even though Excel stores time as numbers under the hood.

The Problem

You paste a column of meeting times from Outlook or a CSV export, and Excel treats them like text. Or worse: it converts them to decimal fractions you can’t read. You try clicking 'Format Cells' — but nothing changes. You type 2:30 PM into A1, press Enter, and see 0.104166667 instead. That’s not broken — it’s Excel being honest about what time really is.

Cell	Raw Input	What Excel Shows	Underlying Value
A1	2:45 PM	0.614583333	=2:45 PM as fraction of day (16.75/24)
A2	17:22	0.723611111	=17:22 as decimal (17.3667/24)
A3	"8:00 AM" (with quotes)	"8:00 AM"	Text — not a time value at all
A4	14:92	#VALUE!	Invalid — Excel rejects malformed time
A5	3:15:44 PM	0.635902778	=15h 15m 44s → 15.2622/24

This isn’t formatting failure — it’s Excel showing its internal representation. The fix isn’t typing harder. It’s telling Excel *what you mean*.

The Solution

Formatting time works only when Excel recognizes the cell content as a time value. If it’s text, no amount of custom format will add math capability. So we fix the data first, then apply formatting.

Select your time column — say B2:B10, which contains entries like 9:15 AM, 16:40, and "08:30:22".
Use Text to Columns: Select B2:B10 → Alt + A → E → choose Delimited → Next → Next → under Column data format, pick Date → MDY → Finish. Wait — that’s for dates. Why does it work? Because Excel’s Date parser handles time-only strings too. This forces conversion from text to true time serial numbers.
Apply time format: Right-click B2:B10 → Format Cells → Category: Time → Choose 1:30 PM or 13:30. Or use this shortcut: Ctrl + 1, then Alt + T, then Enter.
Verify with a simple test: In C2, enter =B2+TIME(0,30,0). If B2 holds 9:15 AM and returns 9:45 AM, you’ve got a real time value.

Before (B2:B6)	After Formatting	Formula Test Result (C2:C6)
"9:15 AM"	9:15 AM	9:45 AM
16:40	4:40 PM	5:10 PM
"08:30:22"	8:30:22 AM	9:00:22 AM
2:05 PM	2:05 PM	2:35 PM
"13:17:00"	1:17 PM	1:47 PM

Notice how "13:17:00" became 1:17 PM, not 13:17. That’s because we picked the 1:30 PM format. Change the format, not the data.

Going Further

Once time values behave, you can do useful things — not just display them.

Add durations: =B2+TIME(1,15,0) adds 1 hour 15 minutes. Don’t use +1:15 — Excel reads that as text unless you wrap it in TIME().
Calculate elapsed time: If D2 = 8:30 AM and E2 = 5:45 PM, =E2-D2 returns 9:15 — no need to convert to decimals.
Custom formats that reveal hidden logic: Try [h]:mm in Format Cells → Custom. If you sum 15 hours + 18 hours, normal h:mm wraps to 9:00; [h]:mm shows 33:00. Brackets disable rollover.
Time zones? Use helper columns: To convert Pacific to Eastern, add TIME(3,0,0) — but only if your source is pure time. If it includes date, use =B2+TIME(3,0,0) — Excel handles date overflow automatically.

Here’s the counterintuitive tip: Don’t use the ‘Time’ category if you need seconds and AM/PM together. The built-in h:mm:ss AM/PM option exists — but it’s buried under ‘Custom’, not ‘Time’. Go to Custom and type h:mm:ss AM/PM manually. Excel’s ‘Time’ gallery omits seconds in AM/PM combos by default.

When NOT to Use This

Time formatting fails when Excel doesn’t recognize the input as time — and sometimes, it shouldn’t.

Shift codes like “D”, “N”, “O”: These aren’t time — they’re labels. Formatting as time won’t help. Use conditional formatting or data validation instead.
Durations over 24 hours entered as “25:30”: Excel accepts this — but only if you type it directly. If imported as text, Text to Columns won’t parse it. Use =TIMEVALUE(SUBSTITUTE(B2,"h","")) or better: =--SUBSTITUTE(B2,"h","") (double-unary forces numeric conversion).
Times with timezone suffixes: “2:30 PM EST” breaks parsing. Strip the suffix first (=SUBSTITUTE(B2," EST","")) before applying Text to Columns.
Legacy systems exporting “1345” as military time: That’s four-digit text, not time. Use =TIME(LEFT(B2,2),RIGHT(B2,2),0) — but verify with =ISNUMBER(...) first.

If your time column contains mixed types — some numbers, some text, some errors — skip formatting entirely until you clean it. Run =ISTEXT(B2) down the column. Filter TRUEs and fix those rows individually.

Keyboard Shortcuts

Action	Shortcut	Notes
Open Format Cells	`Ctrl + 1`	Then navigate with Alt keys — faster than mouse
Apply Time format (1:30 PM)	`Ctrl + Shift + @`	Works only on true time values — not text
Start Text to Columns	`Alt + A → E`	‘A’ opens Data tab, ‘E’ selects Text to Columns
Toggle between time formats	`Ctrl + ;` then `Ctrl + Shift + ;`	Inserts current date/time — handy for testing
Clear formats (reset to General)	`Ctrl + Shift + ~`	Reveals underlying value — essential for debugging