Convert time from #h #m #s format

[chusk1]

I am trying to sort a column by duration of time. The format the cell is in is #h #m #s. This causes it to sort by the first number only.

How can I convert the time to HH:MM:SS format?

 

[Scott Huish]

Excel won't accept what you have provided as a valid number format. But I'm not sure that would cause a problem as the underlying value would still be the same decimal value.
Can you post an example?

 

[GetWorkDone]

If data is in time format multiply it by 86400 which will convert it into seconds and then sort with this coloumn

 

[Rick Rothstein]

If your times are actually text values (instead of time values formatted to look that way), then give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub FixTimeValues()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("A1:A" & LastRow) = Evaluate(Replace("IF(LEN(A1:A#),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A#,""h "","":""),""m "","":""),""s"",""""),"""")", "#", LastRow))
End Sub[/td]
[/tr]
[/table]