I would like to count how many cells are greater-than and less-than a
certain time-frame. I'm using military times formatted in custom as
"hhmm"

Example:

A
1230
1140
1834
0016
0348


Ultimatly would like to end up with:

0600-1759=2
1800-0559=3 (realizing this time frame goes into the next day)
0000-0559=2

Can anyone help?


--
vldavis809
------------------------------------------------------------------------
vldavis809's Profile: http://www.excelforum.com/member.php...o&userid=36146
View this thread: http://www.excelforum.com/showthread...hreadid=570928

"vldavis809" wrote
in message ...

I would like to count how many cells are greater-than and less-than a
certain time-frame. I'm using military times formatted in custom as
"hhmm"

Example:

A
1230
1140
1834
0016
0348


Ultimatly would like to end up with:

0600-1759=2


=SUMPRODUCT(--(A1:A100=600),--(A1:A100<1800))


1800-0559=3 (realizing this time frame goes into the next day)


=SUMPRODUCT(--(A1:A100=1800),--(A1:A100<=2399))+COUNTIF(A1:A100,"<"&600)


0000-0559=2


=COUNTIF(A1:A100,"<"&600)

vldavis809 Wrote:


0600-1759=2
1800-0559=3 (realizing this time frame goes into the next day)
0000-0559=2

Can anyone help?

Try

=SUMPRODUCT((HOUR(A1:A5)=6)*(HOUR(A1:A5)<18))
=SUMPRODUCT((HOUR(A1:A5)=18)+(HOUR(A1:A5)<6))
and
=COUNTIF(A1:A5,"<"&"06:00")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=570928

Thanks, it helped a little.

I can get some of it to work, but not all of it.

This one works great!
=SUMPRODUCT((HOUR(A1:A5)=6)*(HOUR(A1:A5)<18))

This one will not work properly. It gives me a number way off than the
true answer.
=SUMPRODUCT((HOUR(A1:A5)=18)+(HOUR(A1:A5)<6))
If I break it down to
=SUMPRODUCT((HOUR(A1:A5)=18)*(HOUR(A1:A5)<2359))
I can get part of the total
=SUMPRODUCT((HOUR(A1:A5)=1)*(HOUR(A1:A5)<6))
I can get part of the total

The problem is, I need the count of hours between 0000 and 0100.


This one works great!
=COUNTIF(A1:A5,"<"&"06:00")


--
vldavis809
------------------------------------------------------------------------
vldavis809's Profile: http://www.excelforum.com/member.php...o&userid=36146
View this thread: http://www.excelforum.com/showthread...hreadid=570928

vldavis809 Wrote:
This one will not work properly. It gives me a number way off than the
true answer.
=SUMPRODUCT((HOUR(A1:A5)=18)+(HOUR(A1:A5)<6))


If you have blank cells in the range it will count these too. Try

=SUMPRODUCT((HOUR(A1:A5)=18)+(HOUR(A1:A5)<6),--(A1:A5<""))

vldavis809 Wrote:
The problem is, I need the count of hours between 0000 and 0100.


Not sure if you mean this or it's a typo. Just use

=COUNTIF(A1:A5,"<"&"01:00")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=570928