Convert dotted clock codes to real times
Turn the text 14.30.15 or 09.05 into a real time: 2:30:15 PM or 9:05 AM.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | The clock code, with dots between the parts. | "14.30.15" |
| 2 | SUBSTITUTE(A8,".",":") | Swaps each dot for a colon. | "14:30:15" |
| 3 | TIMEVALUE( … ) | Turns that text into a real time. | 2:30:15 PM |
Each bracket wraps the one above it, so Excel works from the inside out. Hours use 24-hour numbering.
=TIMEVALUE(SUBSTITUTE(A8,".",":"))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 codes like 14.30.15 or 09.05 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
Use clock codes only. A decimal hour like 14.5 means half past two, but it comes out as 2:05 PM.
Microsoft's help: SUBSTITUTE function
OTHER ROUTES YOU MIGHT LIKE

Read a clock with AM/PM in parentheses
2:30:15(PM) → 2:30:15 PM
Get the template →
Read a 24-hour clock like 14h30m
14h30m → 2:30 PM
Get the template →
Clean hidden spaces and tabs in times
2:30:15[nonbreaking space]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 →