Items by hour using date values issue

[WhatIsExcel??!]

I have an issue that I know the theory behind but lack the VB/Excel knowledge to conquer: I have a column of dates/times that I need to use to calculate tickets created by hour. There are exactly 2804 rows in this column so I am trying to avoid spending an hour doing this manually. The dates are in this format: MM/DD/YY HH:MM AM/PM (Eg. 1/1/16 7:57 AM)

I was thinking of using a simple counting loop to calculate the amount of tickets for each hour, but I am having issues finding the best method to output the count of the tickets created each hour into another cell so that I can get the data I need.

Any help will be much appreciated!

Thanks

[6StringJazzer]

Welcome to the Forum WhatIsExcel!

As a new member, please take the time to review our rules. There aren't many, and they are all important. Our first rule is to use thoughtful thread titles. Every post in this entire subforum is a macro question. Please think about how to boil your question down and summarize it in your title.

We ask this for two reasons:
1. Members scan the list of threads to decide where they can help. The title helps us decide which questions we have the knowledge to solve.
2. After a question is answered, the title helps people with similar questions find it in a search.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[AlphaFrog]

Try something like this. It reads the time values from column A and outputs the results in columns B:C (Rounded hours and counts per hour)

Please Login or Register  to view this content.

[WhatIsExcel??!]

Thank you for your reply AlphaFrog! I failed to fully explain myself so this answer is close to what I need. This gives me great info to reverse engineer and mess around with for now. I tried it out on my document but I either messed it up or my lack of detail did not yield the proper coding.

What I should have explained is that I have is multiple days (a little over 6 months worth) of date/time values in this column. So I need to find a way now to separate the counts out by each individual day. The end goal is to find the ticket created count by hour for each day over a 6 month period. With this we can calculate the average amount of tickets created per hour throughout business hours. I need this data for staffing justification and I hope to get myself a more advantageous shift by showing the data

Thank for the help!

Example data from the column I am speaking of:
Created Time
1/1/16 7:57 AM
1/4/16 7:13 AM
1/4/16 7:25 AM
1/4/16 7:53 AM
1/4/16 8:08 AM
1/4/16 8:10 AM
1/4/16 8:20 AM
1/4/16 9:02 AM
1/4/16 9:17 AM
1/4/16 9:21 AM
1/4/16 10:18 AM
1/4/16 10:20 AM
1/4/16 10:27 AM
1/4/16 10:34 AM
1/4/16 10:52 AM
1/4/16 11:03 AM
1/4/16 11:22 AM
1/4/16 1:17 PM
1/4/16 2:24 PM
1/4/16 2:25 PM
1/4/16 4:24 PM
1/4/16 4:38 PM
1/5/16 7:45 AM
1/5/16 8:33 AM
1/5/16 9:14 AM
1/5/16 9:17 AM
1/5/16 10:00 AM
1/5/16 11:46 AM
1/6/16 6:41 AM
1/6/16 6:50 AM
1/6/16 7:53 AM
1/6/16 9:07 AM
1/6/16 9:41 AM
1/6/16 11:06 AM
1/6/16 11:19 AM
1/6/16 11:50 AM
1/6/16 12:14 PM
1/6/16 12:44 PM
1/6/16 12:58 PM

[AlphaFrog]

With the example data, what would the output be?

[WhatIsExcel??!]

So the output for the first two hours on 1/4/16 in my example would be "1/4/16 7:00 AM" in one column and "4" in the second column, then "1/4/16 8:00 AM" in one column and "3" in the next. Essentially counting how many tickets came in each hour that day. Not even sure if this is possible but it is what I need to do with the data. This way we can show the trend for tickets created each hour during business hours and justify shifting the employee work schedule to match the highest volume ticketing hours.

Thanks again!

[AlphaFrog]

That's what I got using the suggested code (Note the date on the first entry is different).

Created Time____Hr_____________Count
1/1/2016 7:57___1/1/2016 7:00___1
1/4/2016 7:13___1/4/2016 7:00___3
1/4/2016 7:25___1/4/2016 8:00___3
1/4/2016 7:53
1/4/2016 8:08
1/4/2016 8:10
1/4/2016 8:20

[WhatIsExcel??!]

I must have messed it up when I applied it to my document then. It ended up changing the Created Time column to the rounded hours and that is why I was confused. I will try again and let you know how it goes.

[WhatIsExcel??!]

Weird... I get the results close to what you posted above, but the column that rounds the hour is increasing by an increment of 1 each time there is more than one count for an hour. I have attached my worksheet for reference.

Thanks!

[AlphaFrog]

Looks good to me. What are you saying is the problem? Can you give a specific value that is wrong?

[WhatIsExcel??!]

Maybe I am misinterpreting what the output should be according to your code so please forgive me if that is the case. My understanding of what the output value should be for cells B3-5 would mean that they should all be "1/4/2016 7:00 AM". Yet they are "1/4/2016 7:00 AM", "1/4/2016 8:00 AM", and "1/4/2016 9:00 AM" respectively. I also just realized that the macro stopped at row 1121 for some reason.

Maybe I need to rethink my approach since even if the code outputs what I was expecting I still have to tally most of this up manually to get the end result. I am going to look into some pivot tables or something to try and wrangle this data.

I appreciate your help and quick relies!