Rewrite clock text in a chosen format

Turn clock text like 6:45 PM into 18:45:00, using the format typed in column B.

What you'll make

In Excel: 2:30:15 PM with hh:mm:ss becomes 14:30:15.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TIMEVALUE(A8)Reads the clock text as a real time.2:30:15 PM
2B8The format to write the time in."hh:mm:ss"
3TEXT( … )Writes the time in that format as text."14:30:15"

Read it left to right. Part 1 sits inside part 3, so Excel reads the time first.

=TEXT(TIMEVALUE(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. On the Template tab, paste clock text like 6:45 PM into column A. Put a format like hh:mm:ss into column B. The results fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Times typed without seconds get :00 in hh:mm:ss. Those zeros are filled in, not measured.

Microsoft's help: TEXT function