1. ## Sorting dates (03)

There's a column in one of my sheets with a long list of time stamps.
I want to know what range of time is the most prevalent. i.e between 8am and 9 am, between 9 am and 10 am, etc, etc.
I tried using a graph but no luck. From what I can tell maybe the graph is sorting by minutes instead of the hours?

Thanks!

2. ## Re: Sorting dates (03)

You can extract out the "hours" using the HOUR function (it will give a number 0-23) for the hour in the day. You can then use the FREQUENCY function or a SUMIF or even a pivot chart to extract out the frequencies.

Steve

3. ## Re: Sorting dates (03)

Hi Steve, thank you. That is exactly what I was needing .

4. ## Re: Sorting dates (03)

Well, I got the combo box example, etc but I am stuck using this one...
I have cells B384:B764 now containing numbers in the range of 1 to 24
In cells G749:G772 I have 1,2,3,4,5, etc all the way to 24

To use the frequency I used =FREQUENCY(B385:B765,G749:G772)
This gives me a total instead of the frequency of each....

Can you tell me what I'm doing wrong?

5. ## Re: Sorting dates (03)

Have you entered it as an array formula (for example in H749:H772), i.e. confirmed it with Ctrl+Shift+Enter?
Alternatively, use the formula
<code>
=COUNTIF(\$B\$384:\$B\$764,G749)
</code>
(for example in H749) to get the frequency of 1, and fill down to get the frequencies for 2, ..., 24.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•