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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Check-in[/TD]
[TD]Check-out[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]6/1/2015[/TD]
[TD]1:16 PM[/TD]
[TD]2:18 PM[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]6/1/2015[/TD]
[TD]10:05 AM[/TD]
[TD]12:01 PM[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]6/2/2015[/TD]
[TD]4:53 PM[/TD]
[TD]6:12 PM[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]6/3/2015[/TD]
[TD]9:32 AM[/TD]
[TD]11:55 AM[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]6/3/2015[/TD]
[TD]8:41 AM[/TD]
[TD]9:30 AM[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]6/4/2015[/TD]
[TD]12:15 PM[/TD]
[TD]2:37 PM[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]6/5/2015[/TD]
[TD]5:43 PM[/TD]
[TD]7:11 PM[/TD]
[TD]N/A[/TD]
[/TR]
</tbody>[/TABLE]














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!
 
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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Check-in[/TD]
[TD]Check-out[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]6/1/2015[/TD]
[TD]1:16 PM[/TD]
[TD]2:18 PM[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]6/1/2015[/TD]
[TD]10:05 AM[/TD]
[TD]12:01 PM[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]6/2/2015[/TD]
[TD]4:53 PM[/TD]
[TD]6:12 PM[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]6/3/2015[/TD]
[TD]9:32 AM[/TD]
[TD]11:55 AM[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]6/3/2015[/TD]
[TD]8:41 AM[/TD]
[TD]9:30 AM[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]6/4/2015[/TD]
[TD]12:15 PM[/TD]
[TD]2:37 PM[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]6/5/2015[/TD]
[TD]5:43 PM[/TD]
[TD]7:11 PM[/TD]
[TD]N/A[/TD]
[/TR]
</tbody>[/TABLE]














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

S
  • Question Question
VBA Separate Day and Time to Another Column if condition meet
2
Replies
10
Views
165
SamKhem
S
S
  • Solved
Number of daytime hours falling between two date ranges
Replies
8
Views
184
Cubist
Cubist
JenniferMurphy
  • Solved
Is there a way to format date-time with proper alignment?
Replies
8
Views
587
JenniferMurphy
JenniferMurphy
K
  • Question Question
How to Report on Time by Month/Year when time data only shows week start date and time by Days in the week
Replies
1
Views
88
JamesCanale
JamesCanale
K
  • Question Question
Confusing Date Placement for an excel worksheet I use for a video game.
Replies
2
Views
74
madelineharris
M

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