Read duration text like PT49H30M15S
Turn duration text with H, M and S markers, like PT49H30M15S, into the real duration 49:30:15.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | TEXTBEFORE(TEXTAFTER(A8,"PT"),"H")/24 | Takes the hours between PT and H, divided by 24. | 49:00:00 |
| 2 | TEXTBEFORE(TEXTAFTER(A8,"H"),"M")/1440 | Adds the minutes between H and M. | 49:30:00 |
| 3 | TEXTBEFORE(TEXTAFTER(A8,"M"),"S")/86400 | Adds the seconds between M and S. | 49:30:15 |
Excel counts time in days, so each number is divided by the hours, minutes or seconds in a day.
=TEXTBEFORE(TEXTAFTER(A8,"PT"),"H")/24+TEXTBEFORE(TEXTAFTER(A8,"H"),"M")/1440+TEXTBEFORE(TEXTAFTER(A8,"M"),"S")/86400How 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 duration text like PT49H30M15S into the green column of the Template tab. The durations fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Each row needs hours, minutes and seconds, even zeros, like PT1H30M0S. Text with days, like P1DT2H, turns amber.
Microsoft's help: TEXTBEFORE function
OTHER ROUTES YOU MIGHT LIKE

Read days and clock text like 2 01:30:15
2 01:30:15 → a 49:30:15 duration
Get the template →
Read elapsed time text like 49h 30m
49h 30m → an elapsed duration over two days
Get the template →
Read elapsed hours text like 49,5
49,5 hours → 49:30 elapsed
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →