Convert time string/text to time (calculable)

Question

I have a column of data which consists of strings such as follows:

Row 1: 46m 06s.

Row 2: 15d 5h 09m 33s

Row 3: 17h 24m 59s

Etc

I want to convert these strings to number or time format so I can perform calculations on them, but the current format makes various formula challenging to use. Ideas?

Answer 1

Public Function convert_text_to_interval(interval As String) As Double
Dim temp() As String, tmp As Variant
On Error GoTo error_handler
temp = Split(interval)
For Each tmp In temp
    Select Case Right(Trim(tmp), 1)
        Case "d"
            convert_text_to_interval = convert_text_to_interval + Val(tmp)
        Case "h"
            convert_text_to_interval = convert_text_to_interval + Val(tmp) / 24#
        Case "m"
            convert_text_to_interval = convert_text_to_interval + Val(tmp) / 1440#
        Case "s"
            convert_text_to_interval = convert_text_to_interval + Val(tmp) / 86400#
    End Select
Next
Exit Function

error_handler:
convert_text_to_interval = 0
End Function

The function must be inserted into a common module (not to a sheet or class module!).

After insertion it will be available in formula master in User-defined functions section. Or it can be inserted directly (for example, as =convert_text_to_interval(A1)).

The spaces between interval parts are compulsory (1h30m not allowed, it will be treated as 1m, see below).

The unit letter must be adjacent to a value (1 h 30 m not allowed, it will be treated as 0h 0m, see below).

The parts without correct unit letter are ignored. If unit part contains more than 1 letter, the last one is used, all another are ignored (1hm == 1m). All non-digit symbols except the last unit letter are ignored.

Multiple parts with the same unit (1h 15m 30m == 1h 45m) are allowed.

Fractional values (decimal separator - dot .) are allowed (1.5h). Negative values are allowed (2h -20m == 1h 40m).

NumberFormat for a cell where this function is used can be both number and time (in last case recommended format is Cell.NumberFormat = "[h]:mm:ss" - it will not cut hours over a day, for example, 15d 5h 09m 33s will be shown as 365:09:33, not 5:09:33 as for "h:mm:ss" format).

Answer 2

Parses a String of text for 'd' 'h' 'm' 's' values and converts to time.

This will not replace the string values. Instead, use this formula to create a helper column for the time arithmetic.

• To enable the display of over 24 hours in in the time formula cells, use the [h]:mm:ss time format.
• All value & letter pairs in the time string are optional. If the letter is present, the value must be attached.
• No space between value and unit letter 3h not 3 h (not: 3 space h)
• Separate one pair from the next with a space. 3d 2h (3d space 2h)
  This is an error: 3d2h (if it has two digits, like the 11 in 3d11h, it should be ok).
• The letters are case insensitive (e.g. 'h' or 'H').

Multiline formatted formula: Paste directly into the formula bar
to avoid splitting the formula over multiple rows.

=VALUE( IFERROR( LEFT( I11, SEARCH( "d", I11) - 1),0) * 24 +
IFERROR( IFERROR( MID( I11, SEARCH( "h", I11) - 2, 2), MID( I11, SEARCH( "h", I11) - 1, 1)), 0) & ":" &
IFERROR( IFERROR( MID( I11, SEARCH( "m", I11) - 2, 2), MID( I11, SEARCH( "m", I11) - 1, 1)), 0) & ":" &
IFERROR( IFERROR( MID( I11, SEARCH( "s", I11) - 2, 2), MID( I11, SEARCH( "s", I11) - 1, 1)), 0))

The value I11 is the cell with the time string (search and replace).
- Or create (insert) helper column.
- Insert new row.
- Paste formula in new row in helper column.
- Cut cell I11 and paste in new row's time string column (assumes I11 is not referenced by an existing formula).
- Copy the contents of original I11 back to the formula bar of I11 so the relative addresses are not moved back, just the contents.

Answer 3

Your issue can be solved by using few Helper Columns also:

How it works:

• Sample data in Range A4:E29.
• D, H, M & S indicates Day, Hour, Minute & Second.

• Formula in B4 & fill down:

  =VALUE(IFERROR(LEFT(A4,SEARCH("d",A4)-1),0)*24)/24

• Formula in C4 & fill down:

  =IF(ISNUMBER(SEARCH(C$3,$A4)),MID($A4,IF(SEARCH(C$3,$A4)<5,1,SEARCH(C$3,$A4)-2),IF(SEARCH(C$3,$A4)<5,SEARCH(C$3,$A4)-1,2)),0)*1

• Formula in D4 & fill down:

  =IF(ISNUMBER(SEARCH(D$3,$A4)),MID($A4,IF(SEARCH(D$3,$A4)<5,1,SEARCH(D$3,$A4)-2),IF(SEARCH(D$3,$A4)<5,SEARCH(D$3,$A4)-1,2)),0)*1

• Formula in E4 & fill down:

  =IF(ISNUMBER(SEARCH(E$3,$A4)),MID($A4,IF(SEARCH(E$3,$A4)<5,1,SEARCH(E$3,$A4)-2),IF(SEARCH(E$3,$A4)<5,SEARCH(E$3,$A4)-1,2)),0)*1

• Final Formula in F4:

=IF(LEN(A4)>1,B4+C4/24+D4/(24*60)+E4/(24*60^2),"")

N.B.

• Adjust cell references in the Formula as needed.
• Cell Format for Range A4:E29 is GENERAL.
• Cell Format for Range F4:F29 is [h]:mm:ss.
• You can hide Helper Columns as soon you get the job done.