How to quantify Check-in/out data Per hour Per day

[jgaschler]

Hi All, First post and couldn't find an answer with the exact solution.

I am trying to pull numbers for a date range of check-in data per hour per day. For example I'd like to see on 6/1/2015 how many people were checked in at 2 pm, 3 pm, 4 pm, so forth.

Here is a small example of the data I have

Date	Check-in	Check-out	Name
6/1/2015	1:16 PM	2:18 PM	N/A
6/1/2015	10:05 AM	12:01 PM	N/A
6/2/2015	4:53 PM	6:12 PM	N/A
6/3/2015	9:32 AM	11:55 AM	N/A
6/3/2015	8:41 AM	9:30 AM	N/A
6/4/2015	12:15 PM	2:37 PM	N/A
6/5/2015	5:43 PM	7:11 PM	N/A

<tbody>
</tbody>

Its a lot larger of a data set, about 3,500 rows for the month of June so I was thinking of separating the days into sheets. I didn't find an easy way of doing this... and may not be necessary

Then what I wanted to do was sort the "Check-in" Column ascending order. Where I get stuck is figuring out a way to count the rows within a given hour of a day i.e. 10 check-ins on 6/4/2015 during the 4 PM hour. Couldn't figure out if Conditional Formatting or Count formulas would work.

Not sure if there is an easy way to do this. Let me know if you need further details. Thanks in advance!

 

[SpillerBD]

Would you be willing to transform the data? and do you have Power Query available?

 

[jgaschler]

Hi SpillerBD,

As long as I can figure out an easy way to get numbers for check-ins by the hour per day I am willing to try anything. I do have Power Query, though I am not too familiar with it