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.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | LEN(A8) | Counts the digits. LET calls it n. | 6 |
| 2 | TIME( … ) | Builds a time from the digits. LET calls it tm. | 2:30:15 PM |
| 3 | AND( … ) | Checks the time writes back as the same code. | TRUE |
| 4 | IF( … ,tm, … ) | Shows the time, or Review clock code if part 3 fails. | 2:30:15 PM |
| 5 | IFERROR(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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- 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.
- 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
OTHER ROUTES YOU MIGHT LIKE

Read a time only if it fits a pattern
14:30, pattern hh:mm → 2:30:00 PM
Get the template →
Check and read 2026-11-04T14:30:15
2026-11-04T14:30:15 → November 4, 2026 2:30:15 PM, or Review timestamp
Get the template →
Check timestamp text with milliseconds
2026-11-04T14:30:15.123 → 11/4/2026 2:30:15.123 PM, but .12 → Review timestamp
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →