Using Find, Mid, etc to extract time data

[BigPatJennings]

Hi,

I have a report (it's actually from the Office 365 activity center) that spits out durations in an odd way. I'll give a few examples:

P1DT2H9M6S - The P means nothing, and neither does the T. It's saying 1 day, 2 hours, 9 mins, 6 seconds
PT17M27S - again, the P and T can be ignored, and it's 17 minutes 27 seconds
PT0S - this is the default, no time was recorded. Again, you can see the P and T should be ignored.

You can see that it's always in this format: P - (number of days, D) - T - (number of hours, H) - (number of minutes, M) - (number of seconds, S)

I'm trying to extract this information into rows where I can calculate better. Not all the entries have days or hours and I'm struggling to write the code to extract this information into workable data. Can someone help please? I should say that this data is in, say, column P of each row.

 

[alansidman]

Using Power Query/Get and Transform. Bring the Data into PQ Editor. The Editor automatically changed the format

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type duration}})
in
    #"Changed Type"

Book1
	A	B	C	D
1	P1DT2H9M6S			Column1
2	PT17M27S			1.02:09:06
3	PT0S			0.00:17:27
4				0.00:00:00
5
Sheet2

 

[BigPatJennings]

Brilliant! I never thought of that. It works perfectly - I can't tell you how much time I was spending trying to wrote complex formulas here.

Much appreciated