Write a duration as PT49H30M15S

Turn the duration 49:30:15 into PT49H30M15S, keeping every full hour.

What you'll make

In Excel: 49:30:15 becomes PT49H30M15S.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1"PT"&INT(A8*24)&"H"Writes PT, the whole hours, then H."PT49H"
2MINUTE(A8)&"M"Adds the minutes left over, then M."PT49H30M"
3SECOND(A8)&"S"Adds the seconds left over, then S."PT49H30M15S"

Excel counts time in days, so times 24 gives hours. Each part keeps its own letter.

="PT"&INT(A8*24)&"H"&MINUTE(A8)&"M"&SECOND(A8)&"S"

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 durations. Paste durations into the green column of the Template tab. The PT text fills in by itself.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Hours over 23 stay as hours, like PT49H. Check that the system you send to accepts that.

Microsoft's help: INT function