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

**Skill level:** Intermediate

## Video Explanation

## 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)

## 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:

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

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.

This formula does the job

=TIJD(+ALS(+NIET(ISFOUT((VIND.ALLES(+LINKS(E$1;1);$C2;1))))=WAAR;+ALS(+VIND.ALLES(” “;$C2)=2;+WAARDE(+DEEL($C2;1;1));+WAARDE(+DEEL($C2;1;2)));0);+ALS(E2=0;+ALS(+NIET(ISFOUT((VIND.ALLES(+LINKS(F$1;1);$C2;1))))=WAAR;+ALS(+VIND.ALLES(” “;$C2)=2;+WAARDE(+DEEL($C2;1;1));+WAARDE(+DEEL($C2;1;2)));0);+ALS(+NIET(ISFOUT((VIND.ALLES(+LINKS(F$1;1);$C2;1))))=WAAR;+ALS(ISFOUT(+WAARDE(+DEEL($C2;9;3)));+ALS(ISFOUT(+WAARDE(+DEEL($C2;8;3)));+WAARDE(+DEEL($C2;7;3));+WAARDE(+DEEL($C2;8;3)));+WAARDE(+DEEL($C2;9;3)));0));+ALS(+NIET(ISFOUT((VIND.ALLES(“con”;$C2))))=WAAR;+WAARDE(+LINKS(+ALS(LINKS(+RECHTS($C2;10);1)=”s”;+RECHTS($C2;9);+RECHTS($C2;10));3));0))

=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))

HI,

that was an easy PQ task.: split column, add column with if, merge columns with delimiter :, format as time

let

Source = Excel.Workbook(File.Contents(“\\Mac\Home\Downloads\Text-to-Time-Challenge-Excel-Campus.xlsx”), null, true),

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″

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),

#”Added Custom1″ = Table.AddColumn(#”Added Custom”, “minute”, each try if Text.Contains([Time.2], “minute”) then [Time.1]

else if Text.Contains([Time.4], “minute”) then [Time.3]

else if Text.Contains([Time.6], “minute”) then [Time.5]

else 0 otherwise 0),

#”Added Custom2″ = Table.AddColumn(#”Added Custom1″, “second”, each try if Text.Contains([Time.2], “second”) then [Time.1]

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”

=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

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.

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))

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

then first answer would be =Answer(C1)

Function Answer(rg As Range)

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

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

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

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

https://www.myspreadsheetlab.com/blog/

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

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.

=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.

‘=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.

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.