Tjyylli

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?

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

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.

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.

• Cell Format for Range B4:E29 is GENERAL.


• Cell Format for Range F4:F29 is [h]:mm:ss.