Export a time like T14:30:15

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

What you'll make

In Excel: 2:30:15 PM becomes T14: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
2"T"The letter T that goes in front."T"
3A8The real clock time.2:30:15 PM
4TEXT( … )Writes the time as hours 00 to 23, minutes, seconds."14:30:15"
5IF( … )Leaves the cell blank if part 1 says yes."T14:30:15"

Read it left to right. The pattern "hh:mm:ss" inside part 4 has no AM or PM, so 2 PM shows as 14.

=IF(A8="","","T"&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 export times fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use this only when the system asks for a time with a T in front. No date or time zone is added.

Microsoft's help: TEXT function