Convert a four-digit hhmm clock code
Turn the code 0905 into a real time: 9:05 AM.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | LEFT(A8,2) | Takes the first 2 digits: the hour. | "14" |
| 2 | RIGHT(A8,2) | Takes the last 2 digits: the minutes. | "30" |
| 3 | TIME( … ) | Puts them together as a real time. | 2:30 PM |
Hours use 24-hour numbering, so 14 is 2 PM. The 0 at the end means no seconds.
=TIME(LEFT(A8,2),RIGHT(A8,2),0)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-digit codes like 0930 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 the leading zero, like 0930, and paste codes as text. Codes like 2400 or 1275 roll over to another time instead of turning amber, so check odd ones.
Microsoft's help: TIME function
OTHER ROUTES YOU MIGHT LIKE

Convert a six-digit hhmmss clock code
143015 → 2:30:15 PM
Get the template →
Build a time from hour, minute, second
14 + 30 + 15 → 2:30:15 PM
Get the template →
Convert time text to a real clock time
14:30:15 as text → 2:30:15 PM
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →