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

In Excel: 12:00:00 AM, pattern hhmmss becomes 000000, not 0.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The real clock time.2:30:00 PM
2B8The pattern: hhmm or hhmmss."hhmm"
3TEXT( … )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.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. 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.
  3. 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