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

#### jgaschler

##### New Member
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!

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Takae

##### Well-known Member

F2 = Date
G2 = Check-in time(i.e 13:00)
H2 = Check-out time
F3=COUNTIF(A:A,F2)
G3= SUMPRODUCT((HOUR(\$B\$2:\$B\$5000)=G2*24)*(\$A\$2:\$A\$5000=F2))
H3= SUMPRODUCT((HOUR(\$C\$2:\$C\$5000)=H2*24)*(\$A\$2:\$A\$5000=F2))
Making spreadsheet is possible but I have no idea what type of spreadsheet you want.

#### jgaschler

##### New Member
Hi Takae,

Thanks for the reply, I am making an Excel Spreadsheet so would I just convert the times to Military (24 hour) and plug in these formulas?

#### jgaschler

##### New Member
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!

To be specific and clarify: I am running Excel docs for multiple months. Basically we added later hours for our daycare and I wanted to justify if we were getting enough check-ins to keep these hours. So the results I am hoping to see is on 6/1/2015 we had 8 check-ins at the 5 pm hour, 7 at the 6 pm hour, 4 at the 7 pm hour, etc. Then I can average them out for the week/month.

If its easier I would also be fine with a way of averaging the number of check-ins per hour by the month as well.

#### Takae

##### Well-known Member

Hi,jgaschler
Here is VBA for making spreadsheet.
1.Open a new workbook.
2.Copy the all data to the new workbook.
3.Change the sheet name to Data.
4.Change the sheet2 name to Spreadsheet.
5.Copy the following VBA code to module of new workbook.(Alt+F11)
6. F5 key

Hope this helps.

Code:
``````Sub test()
Dim Dws As Worksheet
Dim SPws As Worksheet, a
Dim i As Long, d As Long, j As Long, LastR As Long, LastRR As Long, cnt As Long
Dim hr
Application.ScreenUpdating = False
Set Dws = Worksheets("Data")
If Dws.AutoFilterMode = True Then
Dws.Range("A1").AutoFilter
End If

'Number of last day
d = Day(DateSerial(Year(Dws.Range("A2")), Month(Dws.Range("A2")) + 1, 0))
LastRR = Dws.Cells(Rows.Count, 1).End(xlUp).Row
With SPws
.Range("A1").Value = "Date"
.Range("B1").Value = "Check-in"
.Range("C1").Value = "Count"
.Range("D1").Value = "Check-out"
.Range("E1").Value = "Count"

j = 2
For i = 1 To d
.Range(.Cells(j, 1), .Cells(j + 23, 1)) = DateSerial(Year(Dws.Range("A2")), Month(Dws.Range("A2")), i)
j = j + 24
Next

LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
'Making columnB and columnC of preadsheet
For j = 1 To LastR - 24 Step 24
For i = 0 To 23
.Cells(i + j + 1, 2).Value = i & ":00"
Next
Next
.Range(.Range("D2"), .Cells(LastR, 4)).Value = .Range(.Range("B2"), .Cells(LastR, 2)).Value
'For average
.Range(.Range("G2"), .Range("G25")).Value = .Range(.Range("B2"), .Range("B25")).Value
.Columns(2).NumberFormatLocal = "h:mm AM/PM"
.Columns(4).NumberFormatLocal = "h:mm AM/PM"
.Columns(7).NumberFormatLocal = "h:mm AM/PM"
End With

'Counting each day of each hour
With Dws
'Check-in
j = 2
For i = 2 To LastR Step 24
For hr = 1 To 24
Dws.Range("A1").AutoFilter Field:=1, Criteria1:=Format(SPws.Cells(i, 1), "m/d/yyyy")
Dws.Range("A1").AutoFilter Field:=2, Criteria1:=">=" & hr - 1 & ":00", Operator:=xlAnd, Criteria2:=" <=" & hr - 1 & ":59"
cnt = WorksheetFunction.Subtotal(3, .Columns(1)) - 1
SPws.Cells(j, 3).Value = cnt
j = j + 1
.ShowAllData
Next
Next
'Check-out
j = 2
For i = 2 To LastR Step 24
For hr = 1 To 24
Dws.Range("A1").AutoFilter Field:=1, Criteria1:=Format(SPws.Cells(i, 1), "m/d/yyyy")
Dws.Range("A1").AutoFilter Field:=3, Criteria1:=">=" & hr - 1 & ":00", Operator:=xlAnd, Criteria2:=" <=" & hr - 1 & ":59"
cnt = WorksheetFunction.Subtotal(3, .Columns(1)) - 1
SPws.Cells(j, 5).Value = cnt
j = j + 1
.ShowAllData
Next
Next
End With
SPws.Range("H2").Value = "=AVERAGEIF(B:B,G2,C:C)"
SPws.Range("H2").Copy SPws.Range("H3:H25")
Application.ScreenUpdating = True
End Sub``````

#### jgaschler

##### New Member
Thank you so much Takae! This worked perfectly!!!!!

#### Takae

##### Well-known Member
Hi jgaschler,

Code:
``````[/COLOR]Sub test()Dim Dws As Worksheet
Dim SPws As Worksheet, a
Dim i As Long, d As Long, j As Long, LastR As Long, LastRR As Long, cnt As Long
Dim hr
Application.ScreenUpdating = False
Set Dws = Worksheets("Data")
If Dws.AutoFilterMode = True Then
Dws.Range("A1").AutoFilter
End If

'Number of last day
d = Day(DateSerial(Year(Dws.Range("A2")), Month(Dws.Range("A2")) + 1, 0))
LastRR = Dws.Cells(Rows.Count, 1).End(xlUp).Row
With SPws
.Range("A1").Value = "Date"
.Range("B1").Value = "Check-in"
.Range("C1").Value = "Count"
.Range("D1").Value = "Check-out"
.Range("E1").Value = "Count"

j = 2
For i = 1 To d
.Range(.Cells(j, 1), .Cells(j + 23, 1)) = DateSerial(Year(Dws.Range("A2")), Month(Dws.Range("A2")), i)
j = j + 24
Next

LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
'Making columnB and columnC of preadsheet
For j = 1 To LastR - 24 Step 24
For i = 0 To 23
.Cells(i + j + 1, 2).Value = i & ":00"
Next
Next
.Range(.Range("D2"), .Cells(LastR, 4)).Value = .Range(.Range("B2"), .Cells(LastR, 2)).Value
'For average
.Range(.Range("G2"), .Cells(d, 7)).Value = [B].Range(.Range("B2"), .Cells(d, 2)).Value[/B]
.Columns(2).NumberFormatLocal = "h:mm AM/PM"
.Columns(4).NumberFormatLocal = "h:mm AM/PM"
.Columns(7).NumberFormatLocal = "h:mm AM/PM"
End With

'Counting each day of each hour
With Dws
'Check-in
j = 2
For i = 2 To LastR Step 24
For hr = 1 To 24
Dws.Range("A1").AutoFilter Field:=1, Criteria1:=Format(SPws.Cells(i, 1), "m/d/yyyy")
Dws.Range("A1").AutoFilter Field:=2, Criteria1:=">=" & hr - 1 & ":00", Operator:=xlAnd, Criteria2:=" <=" & hr - 1 & ":59"
cnt = WorksheetFunction.Subtotal(3, .Columns(1)) - 1
SPws.Cells(j, 3).Value = cnt
j = j + 1
.ShowAllData
Next
Next
'Check-out
j = 2
For i = 2 To LastR Step 24
For hr = 1 To 24
Dws.Range("A1").AutoFilter Field:=1, Criteria1:=Format(SPws.Cells(i, 1), "m/d/yyyy")
Dws.Range("A1").AutoFilter Field:=3, Criteria1:=">=" & hr - 1 & ":00", Operator:=xlAnd, Criteria2:=" <=" & hr - 1 & ":59"
cnt = WorksheetFunction.Subtotal(3, .Columns(1)) - 1
SPws.Cells(j, 5).Value = cnt
j = j + 1
.ShowAllData
Next
Next
End With
SPws.Range("H2").Value = "=AVERAGEIF(B:B,G2,C:C)"
SPws.Range("H2").Copy [B]SPws.Range(SPws.Range("H3"), SPws.Cells(d, 8))[/B]
Application.ScreenUpdating = True

End Sub[COLOR=#333333]``````

#### jgaschler

##### New Member
Gotchya, thanks for double checking!

Replies
1
Views
49
Replies
1
Views
284
Replies
3
Views
125
Replies
1
Views
232
Replies
1
Views
315

### Forum statistics

1,136,322
Messages
5,675,068
Members
419,549
Latest member
EliteBeat ### 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.

### Which adblocker are you using?    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

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