Write a duration as PT49H30M15S
Turn the duration 49:30:15 into PT49H30M15S, keeping every full hour.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | "PT"&INT(A8*24)&"H" | Writes PT, the whole hours, then H. | "PT49H" |
| 2 | MINUTE(A8)&"M" | Adds the minutes left over, then M. | "PT49H30M" |
| 3 | SECOND(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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own durations. Paste durations into the green column of the Template tab. The PT text fills in by itself.
- 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
OTHER ROUTES YOU MIGHT LIKE

Write minutes:seconds text like 2970:15
49:30:15 → 2970:15
Get the template →
Read 49.30 as 49 hours 30 minutes
49.30 (hours.minutes) → 49:30
Get the template →
Read negative elapsed time like -0:30
-0:30 → a negative 30-minute numeric duration
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →