Show a 24-hour time including seconds

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

What you'll make

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

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:15 PM
3"hh:mm:ss"The pattern: hours 00 to 23, minutes, seconds."hh:mm:ss"
4TEXT( … )Writes the time in that pattern as text."14:30:15"
5IF( … )Leaves the cell blank if part 1 says yes."14:30:15"

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

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

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

Midnight shows as 00:00:00; it's a real time, not a missing one. The result is text, so sort by the original time.

Microsoft's help: TEXT function