# Data Cleansing Challenge: Convert Text to Time Values

Bottom line: Take on this Excel challenge to convert text to time values.

Skill level: Intermediate

## Video Explanation

Watch on YouTube and give it a thumbs up.

You can download the Excel file that contains the data for the challenge below.

## The Challenge

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.

### The Rules

There are only two simple rules for this challenge:

1. You can use any Excel tools/features you'd like (formulas, Power Query, VBA, etc.).
2. The cells with the results must contain time values. See this article on the Date System in Excel for further explanation.

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! 🙂

• Rand() guy says:

I’m surprised noone suggested a solution using regular expression. In my opinion that is much more elegant and easier to maintain and modify.

Simply add function and call it in column on the sheet.

Function TextToTime(str As String) As Date

Dim dur As Date
Dim hours As Integer, mins As Integer, seconds As Integer

Dim Matches As Object
Dim re As Object
Set re = CreateObject(“vbscript.regexp”)

re.Pattern = “^(?:(\d+) hours?)? ?(?:(\d+) minutes?)? ?(?:(\d+) seconds?)?\$”

Set Matches = re.Execute(str)

hours = CInt(Matches(0).SubMatches(0))
mins = CInt(Matches(0).SubMatches(1))
seconds = CInt(Matches(0).SubMatches(2))

TextToTime = dur

End Function

• jomili says:

I’m trying the UDF on column C, but it’s returning a Value error.

• Hoàng Thông says:

So easy, just use the Autofill function.
1st: Time value is 10 hours 39 minutes 40 seconds. Type 10:39:40 at the right cell
2nd: Time value is 25 seconds. Type 00:00:25 at the right cell
3nd: Click on the right cell of time value and press Ctrl+E and see the result.

• Benzi says:

in row 22 you know what’s wromg

• Jomili says:

Tried your method. “2 hours 10 seconds” came out as “2:10:00”. Many more errors.

• tom says:

This formula does the job

• David N says:

=TIME(IFERROR(MID(C2,FIND(“h”,C2)>0,2),0),IFERROR(MID(C2,MAX(FIND(“m”,C2)-3,1),2),0),IFERROR(MID(C2,MAX(FIND(“se”,C2)-3,1),2),0))

• Thomas says:

HI,
that was an easy PQ task.: split column, add column with if, merge columns with delimiter :, format as time
let
tblExport_Table = Source{[Item=”tblExport”,Kind=”Table”]}[Data],
#”Changed Type” = Table.TransformColumnTypes(tblExport_Table,{{“Name”, type text}, {“Location”, type text}, {“Time”, type text}, {“Time Value”, type datetime}}),
#”Split Column by Delimiter” = Table.SplitColumn(#”Changed Type”, “Time”, Splitter.SplitTextByDelimiter(” “, QuoteStyle.Csv), {“Time.1”, “Time.2”, “Time.3”, “Time.4”, “Time.5”, “Time.6″}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Split Column by Delimiter”,{{“Time.1”, Int64.Type}, {“Time.2”, type text}, {“Time.3”, Int64.Type}, {“Time.4”, type text}, {“Time.5”, Int64.Type}, {“Time.6″, type text}}),
#”Added Custom” = Table.AddColumn(#”Changed Type1″, “Hours”, each if [Time.2] = “hours” then[Time.1] else 0),
#”Added Custom1″ = Table.AddColumn(#”Added Custom”, “Minutes”, each if [Time.2] = “minutes” then [Time.1] else if [Time.4] = “minutes” then [Time.3] else 0),
#”Added Custom2″ = Table.AddColumn(#”Added Custom1″, “Seconds”, each if [Time.2] = “seconds” then [Time.1] else if [Time.4] = “seconds” then [Time.3] else if [Time.6] = “seconds” then[Time.5] else 0),
#”Merged Columns” = Table.CombineColumns(Table.TransformColumnTypes(#”Added Custom2″, {{“Hours”, type text}, {“Minutes”, type text}, {“Seconds”, type text}}, “de-DE”),{“Hours”, “Minutes”, “Seconds”},Combiner.CombineTextByDelimiter(“:”, QuoteStyle.None),”Time”),
#”Changed Type2″ = Table.TransformColumnTypes(#”Merged Columns”,{{“Time”, type time}}),
#”Removed Columns” = Table.RemoveColumns(#”Changed Type2″,{“Time.1”, “Time.2”, “Time.3”, “Time.4”, “Time.5”, “Time.6″}),
#”Changed Type3″ = Table.TransformColumnTypes(#”Removed Columns”,{{“Time Value”, type time}})
in
#”Changed Type3″

• Paul says:

My PQ solution was similar to yours:

let
Source = Excel.CurrentWorkbook(){[Name=”Table2″]}[Content],
#”Split Column by Delimiter” = Table.SplitColumn(Source, “Time”, Splitter.SplitTextByDelimiter(” “, QuoteStyle.Csv), {“Time.1”, “Time.2”, “Time.3”, “Time.4”, “Time.5”, “Time.6″}),
#”Added Custom” = Table.AddColumn(#”Split Column by Delimiter”, “hour”, each try if Text.Contains([Time.2], “hour”) then [Time.1] else if Text.Contains([Time.4], “hour”) then [Time.3] else if Text.Contains([Time.6], “hour”) then [Time.5] else 0 otherwise 0),
else if Text.Contains([Time.4], “minute”) then [Time.3]
else if Text.Contains([Time.6], “minute”) then [Time.5]
else 0 otherwise 0),
else if Text.Contains([Time.4], “second”) then [Time.3]
else if Text.Contains([Time.6], “second”) then [Time.5]
else 0 otherwise 0),
#”Merged Columns” = Table.CombineColumns(Table.TransformColumnTypes(#”Added Custom2″, {{“hour”, type text}, {“minute”, type text}, {“second”, type text}}, “en-US”),{“hour”, “minute”, “second”},Combiner.CombineTextByDelimiter(“:”, QuoteStyle.None),”Duration”),
#”Changed Type4″ = Table.TransformColumnTypes(#”Merged Columns”,{{“Duration”, type duration}}),
#”Removed Columns” = Table.RemoveColumns(#”Changed Type4″,{“Time.1”, “Time.2”, “Time.3”, “Time.4”, “Time.5”, “Time.6″})
in
#”Removed Columns”

• Toni says:

=SUMMPRODUCT(POWER({24,1440,86400},-1);IFERROR(–MID(” “&C2,SEARCH({“hour”,”min”,”sec”},” “&C2)-3,2),0))

my option of function without CSE use
in a formula there can be syntactic mistakes as translated from Russian

• David N says:

That is a very cool and incredibly creative solution! The only tiny problem I see is that the computing of the formula must occasionally accumulate some residual bits because there are 14 instances where Excel thinks the right answer is not the right answer.

Row 979 is an example. The formatted result of this formula is 4:43:23 as expected. However, if you ask Excel if that answer is the same as what is in cell D979, you will get FALSE (no) in response. Even when viewed in decimal format, both times show as 0.196793981. Yet Excel will tell you they are not the same unless your comparison formula includes a ROUND to 9 decimal places.

I know this type of rounding problem is a known, albeit it rare thing with Excel, so I just wanted to point it out for anyone who might be comparing results.

• Bob Brooks says:

Looks like I’m a bit late to the picnic, but:

=TIME(IFERROR(LEFT(C2,FIND(“h”,C2)-2),0),IFERROR(MID(C2,MAX(1,FIND(” m”,C2)-2),2),0),IFERROR(MID(C2,MAX(1,FIND(” se”,C2)-2),2),0))

• Bob Umlas says:

VBA Solution — first format the answer column as h:mm:ss

Dim x As String
Dim temp As Long
x = ” ” & rg
hr = InStr(x, “hour”)
mn = InStr(x, “minute”)
sc = InStr(x, “second”)
If hr > 0 Then temp = 3600 * Left(x, 2)
If mn > 0 Then temp = temp + 60 * Mid(x, mn – 3, 3)
If sc > 0 Then temp = temp + Mid(x, sc – 3, 3)
Answer = temp / 60 / 60 / 24
End Function

• Frank Tonsen says:

let
Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
ReplacedValue = Table.ReplaceValue(Source,” hour”,”/24″,Replacer.ReplaceText,{“Time”}),
ReplacedValue1 = Table.ReplaceValue(ReplacedValue,” minute”,”/1440″,Replacer.ReplaceText,{“Time”}),
ReplacedValue2 = Table.ReplaceValue(ReplacedValue1,” second”,”/86400″,Replacer.ReplaceText,{“Time”}),
ReplacedValue3 = Table.ReplaceValue(ReplacedValue2,”s”,””,Replacer.ReplaceText,{“Time”}),
ReplacedValue4 = Table.ReplaceValue(ReplacedValue3,” “,”+”,Replacer.ReplaceText,{“Time”}),
Evaluate = Table.TransformColumns(ReplacedValue4,{{“Time”, each Expression.Evaluate(_,#shared), type text}}),
ChangedType = Table.TransformColumnTypes(Evaluate,{{“Time”, type time}})
in
ChangedType

• Daniel Valka says:

Hi Jon,
I went down the VBA/UDF route as I got quite intimidated by the idea of having to figure out some complex workbook formula.

My main function looks like this:

Function Convert_Text_To_Time(rng As Range) As Single

Dim time_string As String
time_string = rng.Value

Dim transformed_string As String
transformed_string = Substitute_Hours(time_string)
transformed_string = Substitute_Minutes(transformed_string)
transformed_string = Substitute_Seconds(transformed_string)
transformed_string = Remove_Trailing_Plus(transformed_string)
transformed_string = “=” & transformed_string

Dim time_float As Single
time_float = Evaluate(transformed_string)

Convert_Text_To_Time = time_float

End Function

“Helper” functions like Substitute_Hours() are all similar, simple, single-purpose functions that replace part of the original string by appropriate time fractions, for example:

Function Substitute_Hours(input_string As String) As String

‘substitutes word “hour”/”hours” for string “* 1/24 +”

Dim string_to_replace As String

If input_string Like “*hours*” Then
string_to_replace = “hours”
Else
If input_string Like “*hour*” Then
string_to_replace = “hour”
End If
End If

Substitute_Hours = Replace(input_string, string_to_replace, “* 1/24 +”)

End Function

You end up with a decimal number representing the time. For example 0.444213 representing string “10 hours 39 minutes 40 seconds.”

The last step is to set the appropriate time format for the appropriate column.

Thanks for great and challenging challenge. 🙂

Dan

• Graham says:

I came up with two solutions using VBA. One calculates the time as a fraction of the day, the other uses an array to extract the numbers from the string.
I would note that if you compare the values in your solution to mine, whilst the values shown in the cells are the same there is a small difference in the 15 decimal point…Excel accuracy again!

Graham

Option Explicit

Function ConvertTextToTime1(strText As String) As Double
‘Set target cell to Custom Format hh:mm:ss
Dim intHr As Integer
Dim intMin As Integer
Dim intSec As Integer
Dim intStart As Integer
Dim dblTime As Double

strText = Replace(strText, “s”, “”)

intHr = InStr(strText, “hour”)
intMin = InStr(strText, “minute”)
intSec = InStr(strText, “econd”)
intStart = 1

If intHr > 0 Then
dblTime = Val(Left(strText, intHr – 1)) / 24
intStart = intHr + 5
End If

If intMin > 0 Then
dblTime = dblTime + Val(Mid(strText, intStart, intMin – 1)) / 1440
intStart = intMin + 7
End If

If intSec > 0 Then
dblTime = dblTime + Val(Mid(strText, intStart, intSec – 1)) / 86400
End If

ConvertTextToTime1 = dblTime

End Function

Function ConvertTextToTime2(strText As String) As Double
‘Set target cell to Custom Format hh:mm:ss
Dim arrText() As String
Dim intCount As Integer
Dim strHr As String
Dim strMin As String
Dim strSec As String

arrText = Split(strText)
strHr = “0”
strMin = “0”
strSec = “0”

For intCount = 1 To UBound(arrText) Step 2
Select Case arrText(intCount)
Case “hours”, “hour”
strHr = arrText(intCount – 1)
Case “minutes”, “minute”
strMin = arrText(intCount – 1)
Case “seconds”, “second”
strSec = arrText(intCount – 1)
End Select
Next intCount

ConvertTextToTime2 = TimeValue(strHr & “:” & strMin & “:” & strSec)

End Function

• Hi Jon,
Here’s my solution:
=TIME(IFERROR(MID(” “&C2,FIND(“h”,” “&C2)-3,3)+0,0),IFERROR(MID(” “&C2,FIND(“m”,” “&C2)-3,3)+0,0),IFERROR(MID(” “&C2,FIND(“se”,” “&C2)-3,3)+0,0))
Thanks for the challenge!
Cheers,
Kevin Lehrbass

• Edil says:

Dear Jon,

I have send you an Excel file with my solution. In the file, I broke down the formula in three parts, extracting the hours, the minutes and the seconds values. The combined formula results in the following:

SOLUTION 1:
=TIME(IFERROR(LEFT(C2,FIND(“hour”,C2)-2),0),RIGHT(IFERROR(LEFT(C2,FIND(“min”,C2)-2),0),2),RIGHT(IFERROR(LEFT(C2,FIND(“sec”,C2)-2),0),2))

SOLUTION 2:
=TIME(IFERROR(LEFT(C2,IFERROR(FIND(“hour”,C2),0)-2),0),IFERROR(MID(C2,IFERROR(FIND(“min”,C2),0)-3,2),0),IFERROR(MID(C2,IFERROR(FIND(“sec”,C2),0)-3,2),0))

It was an interesting challenge, thx Jon

• David N says:

Your first formula works, but your second formula does not correctly handle the 16 instances where the text begins with a single digit number of minutes.

• Paul Lou says:

=TIME(VALUE(IFERROR(MID(C2,IF(SEARCH(“h”,C2)<5,1,2),2),"0")), VALUE(IFERROR(MID(C2,IF(SEARCH("m",C2)=3,SEARCH("m",C2)-2,SEARCH("m",C2)-3),2),"0")),VALUE(IFERROR(MID(C2,SEARCH("sec",C2)-3,2),"0")))

This work perfectly.

• Paul Lou says:

‘=TIME(VALUE(IFERROR(MID(C2,IF(SEARCH(“h”,C2)<5,1,2),2),"0")), VALUE(IFERROR(MID(C2,SEARCH("m",C2)-3,2),"0")),VALUE(IFERROR(MID(C2,SEARCH("sec",C2)-3,2),"0")))

I use the above formula and it works perfectly.

• David N says:

This formula does not correctly handle the 16 instances where the text begins with a single digit number of minutes, but the other formula you posted works for all cases.

Generic filters
Exact matches only
Filter by Custom Post Type