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.
YouTube Subscribe Logo Excel Campus

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.

Times Stored as Text in Excel

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.

Convert Text Values to Time Values in Excel

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.

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

80 comments

Your email address will not be published. Required fields are marked *

  • 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

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

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly