count without bin?? time in wrong format.

[yinxzon]

Hello,
Not sure if this requires a formula. Column C is the answer and counts the number of times taxis arrive before the time in column B. For example, 3 means that 3 taxis arrived before 6:57.
So basically I am trying to count the number of times taxis arrive (see the sheet attached).
Again, I need to count the number of times it arrived before 6:57 (30 minutes from the starting time 6:27), which is C2.
However, column D is not in time format and while I could change column B to remove the colon, i dont think it can be compared to column D. In addition, I need to do this to multiple sheets and it would be inconvienent to add 30 minutes to 6:27 if it is not in time format (i think).
I know its not hard to do manually but I have to do this many many sheets and will probably make mistakes. Can anyone help write a function so I can get column C (final desired column)?

Thanks!!

[yinxzon]

here is the excel.

[shg]

	b	c	d	e
1	6:27	5	0023	c1: =sumproduct(--(text($d$1:$d$69, "0\:00") - b1 <= 0))
2	6:57	5	0023
3	7:27	6	0133
4	7:57	11	0627
5	8:27	11	0627
6	8:57	18	0702
7	9:27	34	0742
8	10:27	49	0742
9	11:27	51	0742
10	12:27	60	0742
11	18:27	69	0746
12			0828
13			0845
14			0845

[yinxzon]

hmm how come you dont get the same answers... i have only 3 before 6:57-- the 3 6:27s.

[yinxzon]

oo and im sorry i removed the date because the original actually has the month and the date on it but i removed it. that was what i was trying to demonstrate in E. i used the right().

For example, the original said 20140905 0627 (3 times) followed by 20140905 0742 4 times...

i guess what im saying is that there is no need sort column d again.. should i keep the date and if i do.. what do i do about the data from the ones next day that would make it wrong.

[shg]

Now and forever, please post the data that's relevant to your question.

[yinxzon]

Here you go (original data on sheet 1).
You can see the steps I took (sort then right()) to get the times but then i lose the date information.
i believe column c is correct and i need the final answer to match that exactly.
thanks!!!