how to convert Timestring to seconds

Question

I have CSV file with around 5000 lines imported into Excel, and my issue is that I've multiple Time strings like 1h1m1s and need to convert them into Seconds.

Here are few example:

Data in Col A

0m11s         
2m32s        
3m10s        
1h2m35s

Result in Col B

11 seconds
152 seconds  
190 seconds    
3755 seconds

I've tried to format cells but end up with some weird results!

Is it possible to do so and if yes, then how could I convert Time String into Seconds?

Answer 1

This is relatively easy using VBA.

One way is to use Regular Expressions to parse the string, then multiply each section by the appropriate conversion.

To enter this User Defined Function (UDF), alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=convSeconds(A1)

in some cell.

This will just return the number of seconds, as shown below. If you want to append the word Seconds, you can either concatenate the formula with the string; or use custom number formatting (which will retain the number quality of the result).

Option Explicit
Function convSeconds(s As String) As Long
    Dim RE As Object, MC As Object
    Dim SEC As Long
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .ignorecase = True
    .Pattern = "(?:(\d+)h)?(?:(\d+)m)?(?:(\d+)s)?"
    If .test(s) = True Then
        Set MC = .Execute(s)
        With MC(0)
            SEC = SEC + .submatches(0) * 3600 'hours
            SEC = SEC + .submatches(1) * 60   'minutes
            SEC = SEC + .submatches(2)        'seconds
        End With
    End If
End With
convSeconds = SEC
End Function

Here is an explanation of the Regular Expression

Extract h/m/s

(?:(\d+)h)?(?:(\d+)m)?(?:(\d+)s)?

• Match the regular expression below (?:(\d+)h)?
  • Between zero and one times, as many times as possible, giving back as needed (greedy) ?
  • Match the regex below and capture its match into backreference number 1 (\d+)
    • Match a single character that is a “digit” \d+
      • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
  • Match the character “h” literally h
• Match the regular expression below (?:(\d+)m)?
  • Between zero and one times, as many times as possible, giving back as needed (greedy) ?
  • Match the regex below and capture its match into backreference number 2 (\d+)
    • Match a single character that is a “digit” \d+
      • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
  • Match the character “m” literally m
• Match the regular expression below (?:(\d+)s)?
  • Between zero and one times, as many times as possible, giving back as needed (greedy) ?
  • Match the regex below and capture its match into backreference number 3 (\d+)
    • Match a single character that is a “digit” \d+
      • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
  • Match the character “s” literally s

Created with RegexBuddy

Answer 2

Here's a simple way to do this. It could all be done in one formula, but I've used a few helper columns to avoid repetition.

In your examples, there are always at least place holders for minutes and seconds, but hours are included only if there are hours. The first step looks for an "h". Formula in B1:

=FIND("h",A1)

If there's no "h", it returns an error. Otherwise it returns h's position in the string (assuming the hour value could exceed 9, otherwise you could just take the left character).

Column C peels off the part that will always be the same. The formula in C1:

=IF(ISERROR(B1),A1,MID(A1,B1+1,LEN(A1)))

If no "h" was found, it uses the original string, otherwise it takes everything after the h.

Column D locates the "m". The formula in D1 is similar to the one in B1:

=FIND("m",C1)

Column E uses the pieces to calculate the seconds. The formula in E1:

=IF(ISERROR(B1),0,3600*LEFT(A1,B1-1))+LEFT(C1,D1-1)*60+MID(C1,D1+1,LEN(C1)-D1-1)

If no "h" was found, the hours contribution is zero, otherwise it's 3600 times what was found to the left of the "h". The minutes contribution is 60 times what was found to the left of the "m". The seconds contribution is the number found after the "m" and before the last character (the "s").

You can hide columns B:D. If you really want everything in a single formula, just replace the references to the helper cells with the associated helper cell formula.

Caveat: This formula deals with the characteristics of the data in the question, which always contains minutes and seconds values. For a situation where minutes and or seconds are not necessarily included, a more complicated formula would be required.

Answer 3

I assume input data is located in column A row 1.

You might try the following formula (copy + paste into B1 and drag down as necessary):

=(IF(ISERROR(FIND("h",A1)),0,INT(MID(A1,1,FIND("h",A1)-1)))*3600)+(INT(IF(ISERROR(FIND("h",A1)),LEFT(A1,FIND("m",A1)-1),MID(A1,IF(ISERROR(FIND("h",A1)),0,FIND("h",A1)+1),FIND("m",A1)-FIND("h",A1)-1)))*60)+INT(MID(A1,FIND("m",A1)+1,FIND("s",A1)-FIND("m",A1)-1))

Answer 4

This question already has answers that address the problem in the question, which contains the simplification that minutes and seconds are always included. People may land here with the similar, but more general problem that only the needed time components are included, so any one or more of the components may be missing.

Ron Rosenfeld's great answer already handles that. For readers not familiar with VBA or regex, who might prefer a formula-based solution they can more readily adapt and maintain, the other current answers don't handle the general case. I'll post this formula-based solution as a separate answer because it's more complicated than required for the limited case in this question, and combining it with my other answer would make both solutions less accessible.

This solution also uses some helper columns to avoid formula repetition, but takes a little different approach. Columns B through D each look for the different time component letters. The formulas in the first row:

B1:  =FIND("h",A1)
C1:  =FIND("m",A1)
D1:  =FIND("s",A1)

If the letter is present this return the position of that letter in the original string, otherwise an error. The "s", if present, will always be the last character, but finding its character position simplifies extracting the seconds value, and FIND serves the double purpose of identifying whether it is present.

Column E extracts each time component value, multiplies it by the conversion factor for seconds, and adds them up. It uses MID to extract the values based on the marker locations found in columns B through D.

The complexity is that for minutes and seconds, some or all of the preceding time components may not be present; the formula needs to determine where in the string the current component starts based on which of the preceding components are present. The formula in E1 is:

=3600*IFERROR(LEFT(A1,B1-1),0)+
 60*IFERROR(MID(A1,1+IFERROR(B1,0),C1-1-IFERROR(B1,0)),0)+
 IFERROR(MID(A1,1+MAX(IFERROR(B1,0),IFERROR(C1,0)),D1-1-MAX(IFERROR(B1,0),IFERROR(C1,0))),0)

I've added line breaks to the formula between the time components for readability, so remove them to copy and paste the formula.

Hours is pretty simple. It takes the characters on the left prior to the "h", or zero if "h" was missing, and multiplies it by 3600.

The minutes value can only be preceded by hours. If a minutes value is present, it calculates the start point and length for MID based on whether there is an hours component.

The location of the seconds value, if present, depends on the presence of either or both of the hours and minutes components. The location of the "m", if present, will always be later in the string than the location of the "h", if present. If either is not present the formula uses a value of zero for that component. The MAX function then provides the farthest position in the original string that precedes the seconds value, and determines how much of the string's length is occupied by previous time components.

The helper columns can be hidden. They could be consolidated into the formula in column E, but if you look at how many times those values are used, the resulting formula would be insanely long and difficult to maintain.