Count data between date and time stamps

[jedilefty]

Hello everyone! I have an issue I need help with. I need to count how many transactions are created between a date with a time stamp. Date format is in month/day/year followed by time in 24hr format. Here is what the column in my sheet look like. Any help would be greatly appreciated!!

 

[Ezguy4u]

jed Let's get the ball rollin. Now this usually generates more questions than answers, but we have to start some place. Sometimes the A plus students weigh in and will post a program that does the same as this one, only they will do in 2 lines of code. Anyway let us know if this is a good solution to your problem. Oh I should mention that this only works if you sort the day and time. Like I did. If this is a problem let us know.

Sub Prog1()

Dim Count As Long
Dim LastCell As Long
Dim i As Long
Dim h As Long

LastCell = Cells(Rows.Count, "A").End(xlUp).Row

Count = 1

For i = 4 To LastCell

If Cells(1, 1) < Cells(i, 1) Then

Cells(i, 1).Select
Exit For
End If

Next i

h = i

Count = 1
For i = h To LastCell

If Cells(2, 1) >= Cells(i + 1, 1) Then

Cells(i + 1, 1).Select
Count = Count + 1
End If
Next i

Range("C2") = Count

Range("A1").Select

End Sub

22-07-22.xlsm
	A	B	C
1	7/21/2022 6:08
2	7/21/2022 11:18	count	4
3
4	7/21/2022 5:06
5	7/21/2022 5:07
6	7/21/2022 5:08
7	7/21/2022 5:14
8	7/21/2022 5:17
9	7/21/2022 5:23
10	7/21/2022 5:47
11	7/21/2022 6:08
12	7/21/2022 8:42
13	7/21/2022 11:18
14	7/21/2022 11:18
15	7/21/2022 12:35
16	7/21/2022 12:47
17	7/21/2022 12:48
18	7/21/2022 12:51
19	7/21/2022 13:38
20	7/21/2022 16:16
21	7/21/2022 16:16
22	7/21/2022 20:00
23	7/21/2022 20:36
24	7/21/2022 22:41
25	7/21/2022 23:52
26	7/21/2022 23:52
Data

 

[Alex Blakenburg]

Do the times in the Input boxes for first and last time have dates entered or just times ?
If just times then how does the formula or code know which date to use or is it just counting between those times for all days in that column ?

If it applies to all days you could try something like this.
Where A1 = Shift Start and A2 = Shift End

=COUNT(FILTER($A$4:A26,(MOD($A$4:$A$26,1)>MOD($A$1,1))*(MOD($A$4:$A$26,1)<MOD($A$2,1)),""))

 

[jedilefty]

Do the times in the Input boxes for first and last time have dates entered or just times ?
If just times then how does the formula or code know which date to use or is it just counting between those times for all days in that column ?

If it applies to all days you could try something like this.
Where A1 = Shift Start and A2 = Shift End

=COUNT(FILTER($A$4:A26,(MOD($A$4:$A$26,1)>MOD($A$1,1))*(MOD($A$4:$A$26,1)<MOD($A$2,1)),""))

Thanks Alex!! This worked out perfectly!! Much appreciated!!

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.