Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    New Lounger
    Join Date
    Sep 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting dates (03)

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

  4. #4
    New Lounger
    Join Date
    Sep 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •