Excel - PT0S - ISO 8601

[simon_70]

Hey All

Sorry anther basic question again sorry I am trying to convert this column "Video Duration" it has the cells as PT0S .

We have exported teams messages in our Origination report which came to me like in this format looking at this it is in a ISO 8601 format any ideas how I can get to to read in human English haha 

[#2]

@simon_70 

For audio duration:

=IFS(ISNUMBER(FIND("H",I2)),"",ISNUMBER(FIND("M",I2)),"0:",TRUE,"0:0:")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I2,"PT",""),"H",":"),"M",":"),"S","")

Fill one cell to the right for video duration. Then fill down.

[#3]

@HansVogelaar 

hey thank you so much the only issue is we have "P1DT9" in the cells 

im putting this onto a Powerbi so it need to be a readable format i have tried formatting the column but nothing changes   

[#4]

@simon_70 

The screenshot in your first post had only PT examples, and none with P1D, so I didn't take that into account.

P1DT7H19M26S means 1 day, 7 hours 19 minutes and 26 seconds.

What should the output of the formula for such a value look like?