Read a time only if it fits a pattern

Turn 14:30 into a real time only when it is written exactly as hh:mm.

What you'll make

In Excel: 14:30, pattern hh:mm becomes 2:30:00 PM.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TEXT( … )=A8Rewrites the time in the pattern and compares.TRUE
2TIMEVALUE(A8)Reads the text as a real time.2:30:00 PM
3"Review time pattern"The message when the writing doesn't match.Not used for row 8
4IF( … )Shows the time, or else the message.2:30:00 PM
5IFERROR( … )Shows the message if it isn't a time at all.2:30:00 PM

Leading zeros count. For hh:mm, 9:30 must be written 09:30. Use 24-hour times, without AM or PM.

=IFERROR(IF(TEXT(TIMEVALUE(A8),B8)=A8,TIMEVALUE(A8),"Review time pattern"),"Review time pattern")

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 times like 14:30 and the pattern they should follow, hh:mm or hh:mm:ss, into the Template tab's green columns. 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

Check every Review time pattern row. Rewrite the time to match, like 09:30 instead of 9:30.

Microsoft's help: TIMEVALUE function