countif or sumproduct?

[yinxzon]

Hello,
Not sure if this requires a formula. Column M (highlighted) is the final CORRECT and desired 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.

Here is what I did,
Original in column A, sort to get column E (note: there are ones that not ideal for sort sometimes if its in a different format). Ideally Id exact the time only.
Get column I, its sorted and runs into the next day. Count manually how many times events have occurred before column L.

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 M2 (3 times).
However, column A is not in the format desired and also runs into the second day 5/24/2011 and 5/25/2011.

Can you help with a function that gets to column M? Thanks!

Thanks!

[boopathiraja]

Apply the below formulas to extract the date and time and then arrive your req

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[yinxzon]

Hi, I get 0.5465 and 40268 when I enter your formula...
Can you attach the excel?

[oeldere]

You also need to extract the date from column E.

I made an pivot table of 1 day.

See the attached file.

[Tony Valko]

Hi, I get 0.5465 and 40268 when I enter your formula...
Can you attach the excel?

Maybe you just need to format the cells. In Excel dates and times are really just numbers formatted to look like dates/times for our human consumption.

0.5465 formatted as Time = 1:06:58 PM

40268 formatted as Date = 3/31/2010

[yinxzon]

thanks for that oeldere, but its not working, even if i add 14+11= its not 30.
column M is correct and if i drag your column i and j your second day doesnt work...

[newdoverman]

No longer interested in following the problem.

[yinxzon]

Hi there, for some reason, your date function is wrong because there is only 5/24 and 5/25 and in your column d has 5/27 and 5/28.
My column M is 100% correct.
I attached what Tony and Oeldere asked me to do, but I am only at the date and time (column o and p).

Here is the formula that I thought would work suggested by shg but I dont know how to combine it with my new format...
c1: =sumproduct(--(text($d$1:$d$69, "0\:00") - b1 <= 0))

[oeldere]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Then explain why this result in M2 = 3 (as I expect there the result 5) => see the pivot table.

[yinxzon]

oldere, your answer is correct up to group 6 bc the next answer is 30 not 11+14=25..
if you count the number of times taxis show up before 9:27 you should count 30.
something is wrong there and also your pivot table requires manual work and different sheets it seems like whereas =sumproduct(--(text($d$1:$d$69, "0\:00") - b1 <= 0)) something like that is just one formula.. i have to do this to many many sheets of raw data so i prefer shg's method but he is MIA lately.

[yinxzon]

help anyone?

[yinxzon]

i dont see how m2 could be 3, if its 5 you must be including events from the next day.