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

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | ISNUMBER( … ) | Checks the hour, minute and second are numbers. | TRUE |
| 2 | A8=INT(A8), … C8<60 | Checks whole numbers, hour 1 to 12, others 0 to 59. | TRUE |
| 3 | OR(D8="AM",D8="PM") | Checks the last cell says AM or PM. | TRUE |
| 4 | TIME(MOD(A8,12)+ … ) | Turns 12 into 0, adds 12 for PM, builds the time. | 2:30:15 PM |
| 5 | IFERROR(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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- 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.
- 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
OTHER ROUTES YOU MIGHT LIKE

Write appointment ranges with one date
Same-day start and end → Nov 4, 2026 2:30 PM–3:15 PM
Get the template →
Treat an equal end time as the next day
11/4/2026 2:30:15 PM + 2:30:15 PM → 11/5/2026 2:30:15 PM
Get the template →
Read duration text with 7 decimal places
-2.01:30:15.5000000 → -2.062679 days
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →