Write a time as hhmm or hhmmss
Turn the time 2:30 PM into the text 1430. Midnight stays 0000, not 0.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | The real clock time. | 2:30:00 PM |
| 2 | B8 | The pattern: hhmm or hhmmss. | "hhmm" |
| 3 | TEXT( … ) | Writes the time as text in that pattern. | "1430" |
hh is the hour from 00 to 23, mm the minutes, ss the seconds. Leading zeros stay, so 9:05 AM is 0905.
=TEXT(A8,B8)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 real times and the pattern, hhmm or hhmmss, into the green columns of the Template tab. The codes fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
hhmm drops the seconds, so use hhmmss when they matter. Use this for clock times, not hours worked.
Microsoft's help: TEXT function
OTHER ROUTES YOU MIGHT LIKE

Omit a known missing timestamp
0001-01-01T00:00:00 → blank; 2026-11-04T14:30:15 → 11/4/2026 14:30:15
Get the template →
Read exact four- or six-digit clock codes
143015 → 2:30:15 PM and 2330 → 11:30:00 PM
Get the template →
Read a time only if it fits a pattern
14:30, pattern hh:mm → 2:30:00 PM
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →