-
first post....
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
-
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)))
-
i'm afraid that it doesn't work...
-
Bit more detail, we can't read your mind.
-
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?
-
-
-
-
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]
-
Crossposted
It is considered very rude to cross post WITHOUT providing a link to the other site.
-
1 Attachment(s)
here is a semple
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!!
-
