macro needed

[catch22]

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
	A	B	C	D	E	F	G	H	I	J
1	M	T	W	T	F	S	S	M	T	W
2	11	12	13	14	15	16	17	18	19	20
3	--	--	9s	8s	7s	--	8s	--	--	9s
4	--	--	17m 28s	25s	--	12s	7s	--	--	5s
5	--	--	33m 8s	6s	22s	8s	7s	43s	10s	8s
6	--	--	1h 16m 34s	7s	8s	12s	13s	9s	5s	9s
7	--	--	1h 4m 32s	2m 38s	5s	13m 27s	8s	7s	2m 51s	9s
8	--	--	1h 1m 13s	2m 15s	--	2m 54s	54s	11m 25s	8s	2m 6s
9	--	--	1h 7m 32s	16m 31s	9s	4m 31s	13m 26s	19m 13s	7s	2m 52s
10	9m 54s	--	--	13s	24s	2m 43s	6m 44s	2m 42s	3m 27s	7s
11	--	--	10s	14s	13s	11m 26s	1m 4s	57s	2m 29s	1m 5s
12	--	--	23s	2m 38s	7s	8m 22s	13m 8s	30s	4m 15s	1m 43s
13	18s	9s	14s	3m 11s	54s	4m 33s	4m 8s	8s	8s	56s
14	16s	9s	9s	7s	8s	15s	8s	14s	10s	12s
15	12s	36s	9s	9s	15s	8s	10s	8s	9s	8s
16	14s	12s	11s	10s	9s	10s	17s	9s	8s	9s
17	10s	11s	8s	8s	6s	9s	12s	13s	10s	10s
18	13s	12s	9s	11s	8s	9s	11s	9s	8s	14s
19	12s	11s	8s	7s	7s	6s	14s	10s	11s	6s
Sheet1

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

 

[DonkeyOte]

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]

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]

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]

Well at least my formula is shorter!

 

[DonkeyOte]

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]

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