Any way to change "7h 34m 27s" to time in excel

[SomeCallMeGenius]

I have this report which consist of time spent by team on a particular contact. Time is shows as something like this - 7h 34m 27s. I want it to be changed in actual time that excel can understand. Like 7:34:27.

Currently I am using the search/replace option to search for "h " and replace with ":".

Any better way to do this?

 

[mikerickson]

Assuming that you are also doing something similar with "m" and "s", not that I can think of.

 

[Rick Rothstein]

How about a macro? If yes, select all the cells you want to convert and run this one...

Sub FixTime()
  Dim Cell As Range
  For Each Cell In Selection
    Cell.Value = CDate(Replace(Replace(Replace(Cell.Value, "h ", ":"), "m ", ":"), "s", ""))
  Next
End Sub

 

[SomeCallMeGenius]

Great, thats what I am already doing. thanks all for the time

 

[SomeCallMeGenius]

hey, I need little more help with the above macro. This macro changes the cell value to time but I also have to change he value to seconds. Can anyone change this a little bit, so it changes the value from 2h 10m 5s to 7805 seconds.

Thanks for all the help you guys can offer.

-SG

 

[Rick Rothstein]

hey, I need little more help with the above macro. This macro changes the cell value to time but I also have to change he value to seconds. Can anyone change this a little bit, so it changes the value from 2h 10m 5s to 7805 seconds.

Does this macro do what you want?

Sub FixTimeAndMakeIntoSeconds()
  Dim Cell As Range, DT As Date
  For Each Cell In Selection
    If Cell.Value <> "" Then
      DT = CDate(Replace(Replace(Replace(Cell.Value, "h ", ":"), "m ", ":"), "s", ""))
      Cell.Value = 3600 * Hour(DT) + 60 * Minute(DT) + Second(DT)
    End If
  Next
End Sub

 

[SomeCallMeGenius]

Thanks for help, I changed this to following and this one also seem to do the same


Sub FixTime()
Dim Cell As Range
For Each Cell In Selection
Cell.Value = CDate(Replace(Replace(Replace(Cell.Value, "h ", ":"), "m ", ":"), "s", ""))
Cell.Value = Cell.Value * 24 * 60 * 60
Cell.Style = "Normal"
Next
End Sub

 

[Rick Rothstein]

Thanks for help, I changed this to following and this one also seem to do the same

Sub FixTime()
Dim Cell As Range
For Each Cell In Selection
Cell.Value = CDate(Replace(Replace(Replace(Cell.Value, "h ", ":"), "m ", ":"), "s", ""))
Cell.Value = Cell.Value * 24 * 60 * 60
Cell.Style = "Normal"
Next
End Sub

Good point about multiplying the date directly by 86400 (that is, 24*60*60); however I wouldn't hit the cell twice as you have done (once to put the time in and again to multiply it by 86400). Since you are using VB, let the code do everything and just deliver the result to the cell one time...

Sub FixTimeAndMakeIntoSeconds()
  Dim Cell As Range, DT As Date
  For Each Cell In Selection
    If Cell.Value <> "" Then Cell.Value = 86400 * CDate(Replace(Replace(Replace(Cell.Value, "h ", ":"), "m ", ":"), "s", ""))
  Next
End Sub

Note that I added an If..Then test to protect against the selection possibly containing empty cells within the selection. If you really do not think you need that protection, then use this instead...

Sub FixTimeAndMakeIntoSeconds()
  Dim Cell As Range, DT As Date
  For Each Cell In Selection
    Cell.Value = 86400 * CDate(Replace(Replace(Replace(Cell.Value, "h ", ":"), "m ", ":"), "s", ""))
  Next
End Sub

 

[SomeCallMeGenius]

Thanks Rick, this one works quite well

Regards,
-SG

 

[dave3009]

If you have a spare column you could use SUBSTITUTE

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "h ", ":"), "m ", ":"), "s", "")+0

and format as time.