Looking to count using a greater-than

[vldavis809]

I would like to count how many cells are greater-than and less-than
certain time-frame. I'm using military times formatted in custom a
"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

 

[Bob Phillips]

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)

 

[daddylonglegs]

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")

 

[vldavis809]

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")

 

[daddylonglegs]

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<>""))

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")