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.

What you'll make

In Excel: 2 + 30 + p.m. becomes 2:30 PM.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1UPPER(SUBSTITUTE(TRIM(C8),".",""))Drops spaces and dots, then capitals. LET calls it ap."PM"
2OR(ap="AM",ap="PM")Checks that ap is AM or PM.TRUE
3MOD(A8,12)+IF(ap="PM",12,0)Turns 12 into 0, then adds 12 for PM.14
4TIME( … ,B8,0)Puts the hour and minutes together as a real time.2:30 PM
5LET( … 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.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. 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.
  3. 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