Time difference (Formula rqd)

[Rosie]

I have a column of times in the following format '0:14'
(24hr clock)

I want to know how many entries are between 0:00 and 0:59,
1:00 and 1:59 etc

Can anyone suggestion a funky formula ... cheers

 

[Frank Kabel]

Hi Rosie
try (if your times are in column A)
=SUMPRODUCT((A1:A100>TIME(0,0,0))*(A1:A100<TIME(0,59,0))
for the first one

or you may try the following:
- add a helper column (lets say column B) and enter the formula
=HOUR(A1)
and copy down
now you may use a pivot table with this column to count the entries for
each hour

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(A1:A500>=TIME(,,1)),--(A1:A500<TIME(1,,)))

for 00:00:01 to 00:59:59

for the next hour

=SUMPRODUCT(--(A1:A500>=TIME(1,,)),--(A1:A500<TIME(2,,)))

and so on

 

[Peo Sjoblom]

For 23:00 to 24:00 use

=SUMPRODUCT(--(A1:A500>=TIME(23,,)),--(A1:A500<=TIME(23,59,59)))