Validate 12-hour clock fields

Turn 2, 30, 15 and PM into 2:30:15 PM. Hour 0 or minute 60 shows Review.

What you'll make

In Excel: 2 + 30 + 15 + PM becomes 2:30:15 PM.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1ISNUMBER( … )Checks the hour, minute and second are numbers.TRUE
2A8=INT(A8), … C8<60Checks whole numbers, hour 1 to 12, others 0 to 59.TRUE
3OR(D8="AM",D8="PM")Checks the last cell says AM or PM.TRUE
4TIME(MOD(A8,12)+ … )Turns 12 into 0, adds 12 for PM, builds the time.2:30:15 PM
5IFERROR(IF(AND( … )))Shows the time, or Review 12-hour fields if a check fails.2:30:15 PM

AND needs parts 1, 2 and 3 all TRUE. Otherwise the row shows Review 12-hour fields.

=IFERROR(IF(AND(ISNUMBER(A8),ISNUMBER(B8),ISNUMBER(C8),A8=INT(A8),B8=INT(B8),C8=INT(C8),A8>=1,A8<=12,B8>=0,B8<60,C8>=0,C8<60,OR(D8="AM",D8="PM")),TIME(MOD(A8,12)+IF(D8="PM",12,0),B8,C8),"Review 12-hour fields"),"Review 12-hour fields")

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, second 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

Write AM or PM with no dots. a.m. or p.m. shows Review 12-hour fields, so fix those first.

Microsoft's help: TIME function