first post....

[ranme100]

hello boys and girls...

this is my first post,

i have a database (excel) and i need some help in v.b

the column CLEARANCE TIME is the main column , i need each 10 sec of CLEARANCE TIME to be translated into the column SECTION (sec) like this

for example : if the CLEARANCE TIME is 00:00:05 (5 sec) then in the column SECTION (sec) it will show 0-10 ( zero to ten seconds)...

and : if the CLEARANCE TIME is 00:01:06 (1 min and 6 sec) then in the column SECTION (sec) it will show 60-70 ( 60 sec to 70 sec)

i have done it by hand and would appreciated a vb program in excel...!

a pic of the database - http://tinypic.com/view.php?pic=egegkk&s=6
thanks in advance ... ran

[Bob Phillips]

Try this formula

=SUMPRODUCT(--($M$2:$M$200>=(LEFT($Q2,FIND("-",$Q2)-1)/24/60/60)),--($M$2:$M$200<=(MID($Q2,FIND("-",$Q2)+1,9)/24/60/60)))

[ranme100]

i'm afraid that it doesn't work...

[Bob Phillips]

Bit more detail, we can't read your mind.

[ranme100]

like the example : if the CLEARANCE TIME is 00:02:03 (2 min and 3 sec) then in the column SECTION (sec) it will show 120-130
120 sec - 130 sec = 00:02:00 - 00:02:10 (min)

understand?

[ranme100]

did you see the picture?

[Bob Phillips]

Works fine in my tests.

[ranme100]

still dosent work...

[Kenneth Hobs]

Next time, if you just post a sample workbook, it helps us help you more easily and usually results in a faster and more accurate solution.

I don't know what purpose Q serves in xld's formula. I tried using O but it did not help.

If you want a UDF, in a module paste this and then use the formula in the comment for M2 and fill down.

[vba]'=I10Time(M2)
Function I10Time(t As Date) As String
Dim top As Integer
Application.Volatile (False)
top = WorksheetFunction.MRound(t * 24 * 60 * 60, 10)
If top < t * 24 * 60 * 60 Then top = top + 10
I10Time = top - 10 & "-" & top
End Function[/vba]

[Tinbendr]

Crossposted

It is considered very rude to cross post WITHOUT providing a link to the other site.

[ranme100]

i want the that collumn section will be done automaticly

the first row is 10 sec = 00:00:10
and the section for 10 sec is between 0-10 sec...

thank you guys so much!!

[ranme100]

sorry i didnt know...