Build a clock time from 12-hour fields
Turn 2, 30 and p.m. into a real time: 2:30 PM. 12 AM is midnight and 12 PM is noon.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | UPPER(SUBSTITUTE(TRIM(C8),".","")) | Drops spaces and dots, then capitals. LET calls it ap. | "PM" |
| 2 | OR(ap="AM",ap="PM") | Checks that ap is AM or PM. | TRUE |
| 3 | MOD(A8,12)+IF(ap="PM",12,0) | Turns 12 into 0, then adds 12 for PM. | 14 |
| 4 | TIME( … ,B8,0) | Puts the hour and minutes together as a real time. | 2:30 PM |
| 5 | LET( … IF( … )) | Shows the time, or Review AM/PM when part 2 fails. | 2:30 PM |
Excel works from the inside out. a.m., am and AM all count.
=LET(ap,UPPER(SUBSTITUTE(TRIM(C8),".","")),IF(OR(ap="AM",ap="PM"),TIME(MOD(A8,12)+IF(ap="PM",12,0),B8,0),"Review AM/PM"))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 the hour, minute and AM or PM into the green columns 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 need to be 1 to 12. An hour like 14 gives a wrong time, so check odd-looking ones.
Microsoft's help: TIME function
OTHER ROUTES YOU MIGHT LIKE

Build an end timestamp across midnight
11/4/2026 2:30:15 PM + 9:05:00 AM → 11/5/2026 9:05:00 AM
Get the template →
Join date and clock text into a timestamp
4-Nov-2026 + 2:30:15 PM → November 4, 2026 2:30:15 PM
Get the template →
Convert a four-digit hhmm clock code
0905 → 9:05 AM
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →