Using Find, Mid, etc to extract time data

BigPatJennings

New Member
Joined
Aug 25, 2014
Messages
12
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.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,159
Office Version
  1. 365
Platform
  1. Windows
Using Power Query/Get and Transform. Bring the Data into PQ Editor. The Editor automatically changed the format

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

Book1
ABCD
1P1DT2H9M6SColumn1
2PT17M27S1.02:09:06
3PT0S0.00:17:27
40.00:00:00
5
Sheet2
 

BigPatJennings

New Member
Joined
Aug 25, 2014
Messages
12
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top