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

J

jgaschler

New Member
Joined
Jul 8, 2015
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
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.
 
Upvote 0
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?
 
Upvote 0
jgaschler said:
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!
Click to expand...

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.
 
Upvote 0
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
 
Upvote 0
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]
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

Q
  • Solved
Calculating total number of hours within a period, counting only specific days of the week, and specific time intervals.
Replies
8
Views
629
Dave Patton
D
C
  • Question
Ordinary Hours or Penalty Shift
Replies
0
Views
72
ChetanPuri
C
F
  • Question
Count # of values that fall within a certain date range
Replies
1
Views
373
RobP
R
J
  • Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
977
felixstraube
F
F
  • Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
225
freeb1893
F

Forum statistics

Threads
1,214,827
Messages
6,121,812
Members
449,048
Latest member
greyangel23

Share this page

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
Back
Top