macro needed

catch22

New Member
Joined
Nov 4, 2002
Messages
37
hello,

I've got a worksheet with data below. As you can see, the data is in hour: min: sec format.(eg: C6=1h 16m 34s). I want this data to be converted into the format 01:16:34. Could this be done with the help of a macro? any suggestion would be greatly appreciated. thanks
Book3
ABCDEFGHIJ
1MTWTFSSMTW
211121314151617181920
3----9s8s7s--8s----9s
4----17m 28s25s--12s7s----5s
5----33m 8s6s22s8s7s43s10s8s
6----1h 16m 34s7s8s12s13s9s5s9s
7----1h 4m 32s2m 38s5s13m 27s8s7s2m 51s9s
8----1h 1m 13s2m 15s--2m 54s54s11m 25s8s2m 6s
9----1h 7m 32s16m 31s9s4m 31s13m 26s19m 13s7s2m 52s
109m 54s----13s24s2m 43s6m 44s2m 42s3m 27s7s
11----10s14s13s11m 26s1m 4s57s2m 29s1m 5s
12----23s2m 38s7s8m 22s13m 8s30s4m 15s1m 43s
1318s9s14s3m 11s54s4m 33s4m 8s8s8s56s
1416s9s9s7s8s15s8s14s10s12s
1512s36s9s9s15s8s10s8s9s8s
1614s12s11s10s9s10s17s9s8s9s
1710s11s8s8s6s9s12s13s10s10s
1813s12s9s11s8s9s11s9s8s14s
1912s11s8s7s7s6s14s10s11s6s
Sheet1


someone pls help...i'm desperate
This message was edited by catch22 on 2002-11-07 05:02
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Sorry - I am not working on the other macro I thought you were talking of this...

This is not VB (obviously) I am not very good at LTrim etc in VB but perhaps one of the other guys can translate this into VB (this works on the data you posted) I will have a look at the other one now!

Luke


=IF(ISERROR(FIND("s",C4)),"",IF(AND(ISERROR(FIND("m",C4)),FIND("s",C4)=2),"00:00:"&TEXT(LEFT(C4,1),"00"),IF(ISERROR(FIND("m",C4)),"00:00:"&TEXT(LEFT(C4,2),"00"),IF(ISERROR(FIND("h",C4)),"00:"&TEXT(LEFT(C4,FIND("m",C4)-1),"00")&":"&TEXT(MID(C4,LEN(C4)-2,2),"00"),TEXT(LEFT(C4,FIND("h",C4)-1),"00")&":"&TEXT(MID(C4,FIND("m",C4)-2,2),"00")&":"&TEXT(MID(C4,LEN(C4)-2,2),"00")))))
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can do it with a formula:

=VALUE(IF(ISERROR(FIND("h",A1)),"00:","")&IF(ISERROR(FIND("m",A1)),"00:","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"s",""),"m ",":"),"h ",":"))

and format it as h:mm:ss. Change the reference to A1 to suit.
 

catch22

New Member
Joined
Nov 4, 2002
Messages
37
Luke,

Luke, how bout a date? i'd love to date a fantastic brain like you. dont worry. i may be a dumb blonde but i'm a beauty..u bet...smmmoooch

no hard feelings Andrew Poulsom...u were very good too...but too bad, you're location is not on your profile.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Well at least my formula is shorter!
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
your formula may not be the only thing...

ho ho!!

I have had a few offers for help given but I have to say this is a first...flattered! Mr Excel as a lonely hearts...interesting concept - one for the forum?

Catch 22 - I'm taken sorry, but I concur that Andrew's formula was much better and thus he deserves the flattery...!!
 

Whisperer14

Well-known Member
Joined
Nov 6, 2002
Messages
589
First select all of the cells containing the time information and then format them to the correct time format of hh:mm:ss taken from the Time option.

Open a module and insert the following code, run the code and all will be done.

Best wishes

:)


Sub ConvertText2Time()
Dim Lgth As Byte
Dim iRow, iCol As Integer
Dim iStr, jStr As String

iRow = 3
iCol = 1
Do
Do
iStr = Cells(iRow, iCol).Value
If Cells(iRow, iCol) = "--" Then
Cells(iRow, iCol).Value = "0:0:0"
Else
jStr = Replace(iStr, " ", "")
iStr = Replace(jStr, "s", "")
jStr = Replace(iStr, "m", ":")
iStr = Replace(jStr, "h", ":")
Lgth = Len(iStr)
Select Case Lgth
Case 1, 2: jStr = "0:00:" & iStr
Case 3 To 5: jStr = "0:" & iStr
Case 6 To 9: jStr = iStr
End Select
Cells(iRow, iCol) = jStr
End If
iCol = iCol + 1
Loop Until Cells(iRow, iCol) = ""
iCol = 1
iRow = iRow + 1
Loop Until Cells(iRow, iCol) = ""
End Sub
 

Forum statistics

Threads
1,144,734
Messages
5,725,982
Members
422,652
Latest member
Elnene1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top