Count of time values

[GaryU]

Hi Guys, hope you can help me here, it's doing my head in!

I have row with time values in it. I would like to count how many
values occur every 30 minutes. For example,

Here's some of the data:

08:45
08:46
08:46
08:47
08:47
08:50
08:51
08:52
08:54
08:54
08:55
08:56
08:57
08:58
08:58
08:58
08:58
08:59
09:01
09:05
09:05
09:06
09:08

I am trying to find out how many times values >=08:31 to <=09:00,

=09:01 to <=09:30 and so on. So, according to the values above the result would be:

08:31 - 09:00 = 18
09:01 - 09:30 = 5

I have been using =sumproduct and that works with normal numbers, but I
cannot get it to work with these time values, perhaps it's the ":" but
I just don't know anymore.

Help appreciated.

Thanks,
Gary

 

[Max]

See sample construct at:
http://www.savefile.com/files/6235072
CountOfTimeValues_GaryU_wksfn.xls

Assume the source times are in A1:A23

If the times in col A are "text" times,
assuming the limits are placed in B1:C1, B2:C2, etc

Put in D1, copy down:
=SUMPRODUCT((--$A$1:$A$23>=B1)*(--$A$1:$A$23<=C1))
The double negatives "--" will coerce the "text" times in col A to real
times


If the times in col A are *real* times, then we could just use
in D1, copy down:
=SUMPRODUCT(($A$1:$A$23>=B1)*($A$1:$A$23<=C1))

 

[GaryU]

Brilliant! You are a star. Thank you very much for your help, Max. :-D

Gary

 

[Max]

You're welcome, Gary !
Thanks for the feedback ..

 

[GaryU]

Hi Max, me again..!

It does work, kind of. When it gets to the 09 to 10am times the
results are not accurate. Would you be able to look at the file for me
as you may be able to see what's going on?

Thanks,
Gary

 

[Max]

Could you upload a small sample copy of your file via a free filehost*
and then post the **link** to it in response here (the link will be
generated when you "upload" in the filehost (follow the instructions
there), then just copy the link and paste it into your reply to this
post)

*Some free filehosts that could be used:
http://www.flypicture.com/
http://cjoint.com/index.php
http://www.savefile.com/index.php

For cjoint.com (it's in French), just click "Browse" button, navigate
to your folder > select the sample file > Open, then click the button
centred in the page below (labelled "Creer le lien Cjoint") and it'll
generate the link. Then copy & paste the generated link as part and
parcel of your response here.

 

[Max]

Another thought ..

If the times in col A are text,
and assuming the "real" time limits are placed in B1:C1, B2:C2, etc

Try this slightly revised formula in D1:
=SUMPRODUCT(($A$1:$A$23>=TEXT(B1,"hh:mm"))*($A$1:$A$23<=TEXT(C1,"hh:mm")))

Copy D1 down as before

The revised formula will convert the "real" time limits into text times
(the earlier one converts the source text times in col A into real
times,
i.e. the other way around)

Let me know if this version works better for you

 

[GaryU]

Too much hasle that

I've uploaded it to here: http://www.fish-addicts.net/temp/temp.zip

Thanks again for willing to look at it. I'll remove that file when I
hear back from you.

Gary

 

[Max]

Re: the slightly revised version in my other response just posted ..

Try this in H2:
=SUMPRODUCT(($C$2:$C$6377>=TEXT(E2,"hh:mm"))*($C$2:$C$6377<=TEXT(F2,"hh:mm")))

Copy H2 down to H30
Then just do a sum on H2:H30 in H31
H31 will return the correct count of 6376 entries in the source col A

 

[Max]

H31 will return the correct count of 6376 entries in the source col A

Oops, I meant source col C, not A

 

[GaryU]

Max, have tested this today and it works great. Thanks again for your
help.

Gary

 

[Max]

Pleasure`, Gary.
Thanks for the feedback ..