Read duration text like P2DT1H30M15S

Turn text like P2DT1H30M15S or PT45M into a real duration, even with parts left out.

What you'll make

In Excel: P2DT1H30M15S becomes 49:30:15.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1days,IFERROR( … ,0)Takes the days between P and D, or 0.2
2t,IFERROR( … ,"")Keeps the time text after T. LET calls it t."1H30M15S"
3hours, … secs, …Takes the numbers before H, M and S, or 0.1, 30 and 15
4days+hours/24+ … /86400Adds them up as one duration.49:30:15
5LET( … )Works out each number once, then adds them.49:30:15

Excel counts time in days, so hours are divided by 24, minutes by 1440, seconds by 86400.

=LET(days,IFERROR(--TEXTBEFORE(TEXTAFTER(A8,"P"),"D"),0),t,IFERROR(TEXTAFTER(A8,"T"),""),hours,IFERROR(--TEXTBEFORE(t,"H"),0),afterH,IFERROR(TEXTAFTER(t,"H"),t),minutes,IFERROR(--TEXTBEFORE(afterH,"M"),0),afterM,IFERROR(TEXTAFTER(afterH,"M"),afterH),secs,IFERROR(NUMBERVALUE(TEXTBEFORE(afterM,"S"),".",","),0),days+hours/24+minutes/1440+secs/86400)

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

Before you send

Only days, hours, minutes and seconds are read. Weeks, months or years give a wrong result with no warning.

Microsoft's help: LET function