Convert Text to Time Values with Power Query (Data Cleansing Part 3)

This is the third video in a series of solutions for our Data Cleansing Challenge. In this video I explain how to use Power Query to convert the time stored as text into numeric time values in Excel.
Download the Excel file to follow along:

Thanks to everyone that commented on the video and blog post with solutions. In this video we look at a solution posted by Walt.

The solution uses Power Query to extract/split the time components (hours, minutes, seconds into separate columns. We then merge the columns and convert the data type to a duration.

Checkout these articles if you are new to Power Query:

Here are links to the other solution videos:

In the next video we look at how to use VBA UDF's (User Defined Functions) to solve the challenge (video coming soon).
00:00 Introduction
00:31 Solution with Power Query
09:07 The Data Cleansing Challenge

[mdallura]

simple and elegant solution. I often find myself going down an overly complicated path. This solution reminds me to always try and keep it as simple as possible. Thanks

[GrainneDuggan_Excel]

Thank you to both you and Walt! Terrific solution - text between deliminators will solve a lot of my problems

[Luciano_mp]

You brought great solutions, my favorite is Power Query, I think this tool is incredible. Thanks for the brilliant videos!

[rowyourboat]

It's interesting to re-watch this after acquiring a basic understanding of Power Query. When I first saw this video, Power Query was like mumbo jumbo to me, and it all went over my head. This is great content!

[Dude]

This has saved me so many times! Thanks!

[pepito-jj]

Hi there! this tutorial is great. Thank you for your time and sharing your knowledge with us.

[sultanSurya]

This solution helped me. Thanks for sharing

[MrDoubleYa]

This was a very cool challenge and thanks for show casing my solution.

[katerina]

Thank you John, great Power Query exercise

[danielmpinga]

This was great. I was racking my brain on this. Thanks!

[PedroCabraldaCamara]

Very cool trick!! Thanks for sharing Jon

[Vishalsharma-zppc]

Great sir, i am an indian, i watch your all vodeo, u r good trainer noone is like u, 👍👍👍

[wayneedmondson]

Hi Jon.. great video on the possible PQ solutions. I hacked one out myself, but not as nice as what you shared. Thanks for showing me better ways to get the job done.. particularly handling the >24 hour problem. These challenges are super useful and helpful in teaching critical thinking and learning the concepts and steps required to solve problems.. which is what it is all about for getting work done, saving time and generally having fun with EXCEL. Keep them coming. Thumbs up!

[godson]

Awesome. I'll have to play around with that

[VineshParekh]

Hello, this is a great video indeed. Is there a way just to keep in HMS format? This solution coverts into days + HMS if the hours are over 24. I want to keep in hours, minutes and seconds only. Thanks!

[olaspanglund]

Actually the format [h]:mm:ss is not for optional hours but will display more than 24 hours without using days.

[ElectronicBarta]

Helpful video, thanks for sharing. but how can i calculate top 5 duration in a query?

[entertainmentgalaxy]

i think PQ won so far ... thanks for sharing

[omprakashojha]

Thanks. Very well explained. My spreadsheet has overtime calculated basis the difference of multiple times. But, PQ pulls the OT data in date and time format. I only want the overtime in hours, minutes and seconds. Please advise how can I do it.

[mikesison]

I prefer this to using the mid formula.. Thanks for sharing.. What if I wanted to add 2 time values. is this possible? Is there a video for this?