Read exact four- or six-digit clock codes

Turn 143015 or 2330 into a real time. Codes like 2400 or 236000 show Review clock code.

What you'll make

In Excel: 143015 becomes 2:30:15 PM and 2330 becomes 11:30:00 PM.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1LEN(A8)Counts the digits. LET calls it n.6
2TIME( … )Builds a time from the digits. LET calls it tm.2:30:15 PM
3AND( … )Checks the time writes back as the same code.TRUE
4IF( … ,tm, … )Shows the time, or Review clock code if part 3 fails.2:30:15 PM
5IFERROR(LET( … ))Shows Review clock code if anything else goes wrong.2:30:15 PM

Excel works from the inside out. 2400 writes back as 0000, so it fails.

=IFERROR(LET(n,LEN(A8),tm,TIME(LEFT(A8,2),MID(A8,3,2),IF(n=6,RIGHT(A8,2),0)),IF(AND(ISTEXT(A8),OR(n=4,n=6),TEXT(tm,IF(n=4,"hhmm","hhmmss"))=A8),tm,"Review clock code")),"Review clock code")

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 four- or six-digit codes like 0930 or 093015 into the green column 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

Keep codes as text with their leading zeros. A code stored as a number, like 930, shows Review clock code.

Microsoft's help: TIME function