Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts

    Count Time Entries

    Hello,
    I have a user that enters in a date in Column A and a time in Column B. They want to be able to count transactions per hour, per day. The worksheet looks like the sample below:

    Any assistance would be greatly appreciated.

    Thanks!

  2. #2
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Sorry the sample didn't paste the first time, here it is
    Date Time (Local)
    05-01-2015 07:04
    05-01-2015 07:10
    05-01-2015 08:21
    05-01-2015 08:37
    05-01-2015 08:47
    05-01-2015 08:52

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    ..would that be
    'average transactions per hour, per 24-hour day'?
    'average transactions per hour per day, based on start-finish'?

    zeddy

  4. The Following User Says Thank You to zeddy For This Useful Post:

    MOSTATE (2015-06-19)

  5. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 986 Times in 916 Posts
    Or total transactions per hour?

    cheers, Paul

  6. #5
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Thanks for the quick replies, they want to know the total transactions per hour per day.

  7. #6
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    It is based on 24 hour day...see sample data below:
    05-01-2015 12:53
    05-01-2015 13:07
    05-01-2015 13:26

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    The attached example should help.
    You could amend the formulas to use named ranges etc etc etc.

    zeddy
    Attached Files Attached Files

  9. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2015-06-18)

  10. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could also use a pivot table. (See attached) You just need to group the Time field by hours.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  11. The Following User Says Thank You to rory For This Useful Post:

    MOSTATE (2015-06-19)

  12. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy & Rory,

    Both Very Slick!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    A question! What causes the minutes & seconds to be ignored by the pivot table? I recreated the pivot on a new sheet and got the same results but expected it would use the whole time.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I grouped the Time field. If you recreate the pivot, it will use the same cache by default, so you will get the same groupings.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. The Following User Says Thank You to rory For This Useful Post:

    zeddy (2015-06-18)

  16. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    I prefer Rory's pivot table method.
    To match the pivot table results, I fixed my table headings from 0 to 23
    (using 24 will always give a zero count, since you can't have Hour(xxxx)=24)
    ..so as penance, I added some sparkline bars alongside the pivot results.

    zeddy
    Attached Files Attached Files

  17. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Thanks! I had to hunt around the ribbon to find that. Haven't used pivots in a while (Excel 2003).
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  18. #14
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Thanks to all the replies. When I try the Group option on the Pivot table I get an error that states I can't group. I am attaching a more thorough example of what they are working with. Thanks!
    Attached Files Attached Files

  19. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    You cannot Group text entries!
    It's because your time values in column [B] are actually text values, and NOT time values (even though they look like they are)!
    Although your cell formats 'appear' to show this data as hh:mm, they are NOT time values as far as Excel is concerned - they are TEXT
    (you can check this using a formula e.g =ISTEXT(B2) will show as True)
    To convert these text values :
    Enter a 1 in a spare cell.
    Copy this cell.
    Then select the range of entries in column B
    Then use Paste-Special-Multiply
    ..this will now 'convert' these 'text values' to numeric
    Now apply your formats as hh:mm
    Note that the time values in the cell will now be -right-aligned.

    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

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