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.