Show a 24-hour time like 14:30

Turn the time 2:30 PM into the text 14:30. Missing times stay blank.

What you'll make

In Excel: 2:30 PM becomes 14:30.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8=""Checks for an empty cell.No
2A8The real clock time.2:30 PM
3"hh:mm"The pattern: two-digit hours 00 to 23, then minutes."hh:mm"
4TEXT( … )Writes the time in that pattern as text."14:30"
5IF( … )Leaves the cell blank if part 1 says yes."14:30"

Read it left to right. With no AM or PM in the pattern, 2 PM shows as 14.

=IF(A8="","",TEXT(A8,"hh:mm"))

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 into the green column of the Template tab. The 24-hour times fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Seconds are left off, so 2:30:45 PM shows as 14:30. Midnight shows as 00:00; it's a real time.

Microsoft's help: TEXT function