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

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
Joined
Jul 1, 2015
Messages
725
If you want to make a spreadsheet, please ignore.

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

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
Joined
Jul 1, 2015
Messages
725

ADVERTISEMENT

Hi,jgaschler
Here is VBA for making spreadsheet.
Please try it.
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")
Set SPws = Worksheets("Spreadsheet")
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
    'Making heading of spreadsheet
    .Range("A1").Value = "Date"
    .Range("B1").Value = "Check-in"
    .Range("C1").Value = "Count"
    .Range("D1").Value = "Check-out"
    .Range("E1").Value = "Count"
    
    'Making columnA of Spreadsheet
    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
 

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
725
Hi jgaschler,

Sorry,I found mistakes!! Please change!

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")
Set SPws = Worksheets("Spreadsheet")
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
    'Making heading of spreadsheet
    .Range("A1").Value = "Date"
    .Range("B1").Value = "Check-in"
    .Range("C1").Value = "Count"
    .Range("D1").Value = "Check-out"
    .Range("E1").Value = "Count"
    
    'Making columnA of Spreadsheet
    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]
 

Forum statistics

Threads
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.
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