Read duration text like P2DT1H30M15S
Turn text like P2DT1H30M15S or PT45M into a real duration, even with parts left out.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | days,IFERROR( … ,0) | Takes the days between P and D, or 0. | 2 |
| 2 | t,IFERROR( … ,"") | Keeps the time text after T. LET calls it t. | "1H30M15S" |
| 3 | hours, … secs, … | Takes the numbers before H, M and S, or 0. | 1, 30 and 15 |
| 4 | days+hours/24+ … /86400 | Adds them up as one duration. | 49:30:15 |
| 5 | LET( … ) | 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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own durations. Paste duration text like P2DT1H30M15S 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
Only days, hours, minutes and seconds are read. Weeks, months or years give a wrong result with no warning.
Microsoft's help: LET function
OTHER ROUTES YOU MIGHT LIKE

Read duration text like -2.01:30:15
-2.01:30:15 → -2.062674 days
Get the template →
Read a long file time number kept as text
134382762150000000 → November 4, 2026 2:30:15 PM
Get the template →
Read a long tick number kept as text
639293994150000000 → November 4, 2026 2:30:15 PM
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →