Read a 24-hour clock like 14h30m
Turn 24-hour clock text with h and m markers, like 14h30m, into a real time: 2:30 PM.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | TEXTBEFORE(A8,"h") | Takes what comes before the h: the hour. | "14" |
| 2 | TEXTAFTER(A8,"h") | Takes what comes after the h. | "30m" |
| 3 | TEXTBEFORE( … "m") | Keeps what comes before the m: the minutes. | "30" |
| 4 | TIME( … ) | Puts them together as a real time. | 2:30 PM |
Parts 2 and 3 work from the inside out. The 0 at the end means no seconds.
=TIME(TEXTBEFORE(A8,"h"),TEXTBEFORE(TEXTAFTER(A8,"h"),"m"),0)How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own times. Paste clock text like 14h30m into the green column of the Template tab. The times fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Hours run from 0 to 23, so 14h30m is 2:30 PM. Hours over 23 or minutes over 59 roll over instead of turning amber, so check odd ones.
Microsoft's help: TIME function
OTHER ROUTES YOU MIGHT LIKE

Clean hidden spaces and tabs in times
2:30:15[nonbreaking space]PM → 2:30:15 PM
Get the template →
Convert dotted clock codes to real times
14.30.15 → 2:30:15 PM
Get the template →
Read a clock with AM/PM in parentheses
2:30:15(PM) → 2:30:15 PM
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →