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

jgaschler

New Member
Joined
Jul 8, 2015
Messages
7
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

DateCheck-inCheck-outName
6/1/20151:16 PM2:18 PMN/A
6/1/201510:05 AM12:01 PMN/A
6/2/20154:53 PM6:12 PMN/A
6/3/20159:32 AM11:55 AMN/A
6/3/20158:41 AM9:30 AMN/A
6/4/201512:15 PM2:37 PMN/A
6/5/20155:43 PM7:11 PMN/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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,722
Would you be willing to transform the data? and do you have Power Query available?
 

jgaschler

New Member
Joined
Jul 8, 2015
Messages
7
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
 

Forum statistics

Threads
1,140,917
Messages
5,703,168
Members
421,279
Latest member
emzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top