Bottom line: Take on this Excel challenge to convert text to time values.
Skill level: Intermediate
Download the Excel File
You can download the Excel file that contains the data for the challenge below.
Text To Time Challenge - Excel Campus.xlsx (180.8 KB)
This challenge was based on a question from Mark, a member of the Excel Campus community. He exports a report from their training system that contains a column with the total amount of training time that each employee has completed.
The problem is that the time is a text value written in the following format.
## hour(s) ## minute(s) ## second(s)
The values are NOT recognized by Excel as a date/time data type. This makes it very difficult to do any type of calculations and analysis on the data.
For example, we might want to see a summary report with the average time spent by Location. That will be very easy to create once the data is cleaned up.
So the challenge is to convert the text values into time values that Excel recognizes.
There are only two simple rules for this challenge:
- You can use any Excel tools/features you'd like (formulas, Power Query, VBA, etc.).
- The cells with the results must contain time values. See this article on the Date System in Excel for further explanation.
Share Your Solution
Please leave a comment below (or on the YouTube video) that explains your solution.
You can also upload your solution file to a cloud sharing service (OneDrive, Google Drive, Dropbox) and share the link.
Note: After you leave a comment below you might not see it on the post right away. It takes time to be approved and clear the site cache.
A Great Learning Opportunity
I'm excited to see your solution, and believe this will be a great way for us to all learn from each other.
I will create a post and video in the next few weeks that walks through some of the most popular & interesting solutions. Please subscribe to our newsletter to get notified when it is available.
Thanks again and have a nice day! 🙂