Counting Number of Occurences of Various Times

  • Thread starter Thread starter Iain Halder
  • Start date Start date
I

Iain Halder

Hi,

Hope someone can help me here ...

I have a table containg various times in hours and minutes.
I need to count the number of occurences of any given time between one
hour to the next.

example:
count 'number of occurences' of 'any time between 00:00 hours and
01:00 hours' as in "=>00:00 but <01:002"

I've tried all manner of count countif and so on but I cannot get
excel 2003 to do this.

Thanks in advance anybody who can help!

Iain.H
 
Hi!

Thanks for that QUICK reply!!!

I'll give this a shot and see how it goes ...

Thank you again.

Iain.H
 
Hi,

This never really worked.

It just gave me a total count of the cells in the column.

In column 1 I have a column of times in format hh:mm:ss (some are
blank)
In column 2 I have a list set up to run from 00:00 hrs to 23:00 hrs.

In column 2 I need to find out how many times these times occur in
column 1 inside each hour range.

COLUMN 1 COLUMN 2
variable hours No occurences listed
21:00:00 00:00 ... ?
13:34:00 01:00 ... ?
12:23:00 02:00 ... ?

I put your formula into the second column "?" area but as I say it
generates only the total number of cells in the first column. What
might I be doing wrong?

Thanks for your patience!

Iain.H
 
I'd personally go with Frank's pivot table suggestion over continually
plugging in values. But for the sake of argument, is there actually a
problem with e.g.

COUNTIF(A:A,"<02:00")-COUNTIF(A:A,"<01:00")

???,
Andy
 
Hi,

This does not work either ... I'll have a look at the pivot table idea
as you suggest.

Thanks for your help and advice though!

Iain.H
 
Hi,

Frank, your SUMPRODUCT / TIME solution worked as did Andy's other one
referring to COUNTIF.

The reason it did not before was that I had to manually change the
times to hh:mm format from hh:mm:ss. As these changes were made both
of the above methods logged the counts.

Strange one! Perhaps because the original database was done on ACCESS
97 and transferred to an EXCEL 97 spreadsheet before being moved onto
an ACCESS & EXCEL 2003 from where I was trying to do the counting.

I tried to change the original hh:mm:ss format using the FORMAT CELLS
-> NUMBER -> TYPE but the original hour format would not adjust. I
then changed them manually (very tedious and I have another 1000 or so
to change still) and your recommendations worked.

So, THANK YOU gentlemen for your assistance. It is greatly
appreciated.

Iain.H
 
Hi
for the future :-)
The problem was caused that your imported times were actually stored as
'Text' values in Excel. to convert them to real times (also including
seconds) use the following procedure:
- copy an EMPTY cell
- select your importe time range
- goto 'Edit - Paste Special' and choose 'Add'

this should convert your 'Text' times to real time values
 
Hi
thanks for your feedback. See also my reply in the NG for future
importing procedures :-)
 
Back
Top