+ Reply to Thread
Results 1 to 7 of 7

Count time (entries per hour)

  1. #1
    Registered User
    Join Date
    09-23-2008
    Location
    Chicagoland
    Posts
    21

    Question Count time (entries per hour)

    I have a bunch of data and I want to be able to count the number of entries that fall within each of the 24 hour increments in a 24 hour clock. (military time)

    For 12:00:00 all times would be between and including 12:00:00 and 12:59:59

    Column B | Count
    ------------------
    12:00:00 344
    13:00:00 44
    14:00:00 5

    Thanks a million!
    Last edited by canonelan2; 10-07-2008 at 03:23 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577
    I used SUMPRODUCT. I assume all cells where you have times are formatted as time and not text. I attached my example. With the Data in A1:A50, the time reference in column C and the formula in D.
    Please Login or Register  to view this content.
    Does this work for you?
    Attached Files Attached Files
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    09-23-2008
    Location
    Chicagoland
    Posts
    21
    Thanks for your help!

    I tried that formula. It seems to be putting the counts that fall on the hour exactly following with the previous hour. If that doesn't make any sense...


    12:11
    12:21
    12:24
    12:33
    12:44
    12:55
    13:00
    13:00
    13:10
    13:23

    The count for the 12:00 hour should be: 6
    The count for the 13:00 hour should be: 4

    That formula is showing a 12:00 total of 8 (including the two 13:00's) and 2 for 13:00.
    It seems to be doing... 12:00:01 to 13:00:00
    instead of 12:00:00 to 12:59:59


    ideas?

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Just move the = in ChemistB's suggestion, i.e. change to

    =SUMPRODUCT(--($A$1:$A$50<C2)*($A$1:$A$50>=C1))

  5. #5
    Registered User
    Join Date
    09-23-2008
    Location
    Chicagoland
    Posts
    21
    great... that works for all hours except 0:00.

    The result should be only 100 but it's including all empty cells as 0:00 apparently.

    Is there a "only include cells with data" in the count that we can add into that one?

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    You can filter out blanks

    =SUMPRODUCT(--($A$1:$A$50<C2),--($A$1:$A$50>=C1),--($A$1:$A$50<>""))

  7. #7
    Registered User
    Join Date
    09-23-2008
    Location
    Chicagoland
    Posts
    21
    that's it! You guys rock!

    Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculating Time across a 24 hour period
    By jmag in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-19-2007, 12:27 PM
  2. Validating Time entries
    By Paul Cooke in forum Excel General
    Replies: 2
    Last Post: 11-10-2007, 09:38 AM
  3. Attempting to subtract time for a 24 hour period
    By JimM in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-19-2007, 09:51 AM
  4. 24 hour time problem
    By chris1975 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-15-2007, 12:17 AM
  5. count only bold entries
    By jimb0693 in forum Excel General
    Replies: 9
    Last Post: 04-27-2007, 11:44 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1