Countif for finding a Time input

[Meebers]

Got a big long column D containing times formatted as i.e. 13:30. I am
trying to do a countif function for finding the count of inputs between 6 am
and 6:59 am, and 7am and 7:59 etc. Can't find the right combination of
using logic => and < within the countif formula... appreciate any help
here..

 

[Mike H]

Hi,

maybe this for 06:00 to 06:59:59

=SUMPRODUCT((D18>=TIME(6,0,0))*(D18<TIME(7,0,0)))

or you can do this for 06:00 to 06:59:59 and drag down for subsequent hours

=SUMPRODUCT((D18>=TIME(ROW(A6),0,0))*(D18<TIME(ROW(A7),0,0)))
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[T. Valko]

Can't find the right combination of using logic

=> and < within the countif formula...

Like this...

=COUNTIF(A1:A20,">="&TIME(6,0,0))-COUNTIF(A1:A20,">="&TIME(7,0,0))

Might be easier to use this...

=SUMPRODUCT(--(HOUR(A1:A20)=6))

 

[Meebers]

Good One!!

Like this...

=COUNTIF(A1:A20,">="&TIME(6,0,0))-COUNTIF(A1:A20,">="&TIME(7,0,0))

Might be easier to use this...

=SUMPRODUCT(--(HOUR(A1:A20)=6))

 

[Meebers]

Thanks Mike....I used the second one. MikeG

Hi,

maybe this for 06:00 to 06:59:59

=SUMPRODUCT((D18>=TIME(6,0,0))*(D18<TIME(7,0,0)))

or you can do this for 06:00 to 06:59:59 and drag down for subsequent
hours

=SUMPRODUCT((D18>=TIME(ROW(A6),0,0))*(D18<TIME(ROW(A7),0,0)))
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.