PDA

View Full Version : first post....



ranme100
07-31-2012, 02:27 AM
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
07-31-2012, 02:48 AM
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
07-31-2012, 02:57 AM
i'm afraid that it doesn't work...

Bob Phillips
07-31-2012, 03:16 AM
Bit more detail, we can't read your mind.

ranme100
07-31-2012, 03:19 AM
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
07-31-2012, 03:19 AM
did you see the picture?

Bob Phillips
07-31-2012, 03:47 AM
Works fine in my tests.

ranme100
07-31-2012, 05:52 AM
still dosent work...

Kenneth Hobs
07-31-2012, 07:23 AM
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.

'=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

Tinbendr
07-31-2012, 11:50 AM
Crossposted (http://www.excelforum.com/excel-programming-vba-macros/849148-time-division-into-sections.html)

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

ranme100
07-31-2012, 12:59 PM
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
07-31-2012, 01:00 PM
sorry i didnt know...