Sort and count time stamps into a 24 hour period, broken into 30 minute segments.

Hi all. I have an Excel question. I would like a method to sort and count a given amount of time stamps into a 24 hour period, broken into 30 minute segments. The file "Input" has an example input and "Output" is what I would like the end result to be. Like this:

Input:
1/8/2018 23:58
1/8/2018 23:55
1/8/2018 23:49
1/8/2018 23:40
1/8/2018 23:37
1/8/2018 23:18
1/8/2018 23:15
1/8/2018 23:10
1/8/2018 23:08
1/8/2018 23:02
1/8/2018 23:01
1/8/2018 22:54
1/8/2018 22:47
1/8/2018 22:41
1/8/2018 22:35
1/8/2018 22:34
1/8/2018 22:27
1/8/2018 22:25
1/8/2018 22:24
1/8/2018 22:12
1/8/2018 21:48
1/8/2018 21:24
1/8/2018 21:20
1/8/2018 21:15
1/8/2018 21:02
1/8/2018 21:01
1/8/2018 21:01
1/8/2018 21:01
1/8/2018 20:59
1/8/2018 20:56
1/8/2018 20:55
1/8/2018 20:52
1/8/2018 20:16
1/8/2018 20:09
1/8/2018 19:53
1/8/2018 19:42
1/8/2018 19:33
1/8/2018 19:30
1/8/2018 19:30
1/8/2018 19:24
1/8/2018 19:09
1/8/2018 19:09
1/8/2018 19:08
1/8/2018 19:06
1/8/2018 18:57
1/8/2018 18:54
1/8/2018 18:13
1/8/2018 18:11
1/8/2018 17:58
1/8/2018 17:57
1/8/2018 17:39
1/8/2018 17:26
1/8/2018 17:19
1/8/2018 16:57
1/8/2018 16:21
1/8/2018 15:54
1/8/2018 15:51
1/8/2018 15:47
1/8/2018 15:41
1/8/2018 15:33
1/8/2018 15:26
1/8/2018 15:15
1/8/2018 15:15
1/8/2018 14:44
1/8/2018 14:41
1/8/2018 14:37
1/8/2018 14:16
1/8/2018 14:12
1/8/2018 13:55
1/8/2018 13:19
1/8/2018 12:46
1/8/2018 12:24
1/8/2018 12:22
1/8/2018 12:21
1/8/2018 12:19
1/8/2018 12:12
1/8/2018 11:45
1/8/2018 11:40
1/8/2018 11:29
1/8/2018 11:07
1/8/2018 10:54
1/8/2018 10:53
1/8/2018 10:47
1/8/2018 10:18
1/8/2018 9:59
1/8/2018 9:54
1/8/2018 9:49
1/8/2018 9:47
1/8/2018 9:45
1/8/2018 9:31
1/8/2018 9:30
1/8/2018 9:15
1/8/2018 9:07
1/8/2018 8:57
1/8/2018 7:34
1/8/2018 6:57
1/8/2018 4:54
1/8/2018 3:33
1/8/2018 2:15
1/8/2018 1:53
1/8/2018 1:08
1/8/2018 0:53
1/8/2018 0:50

Output:
12:00 AM      12:30 AM      0
12:30 AM       1:00 AM      2
1:00 AM               1:30 AM      1
1:30 AM               2:00 AM      1
2:00 AM               2:30 AM      1
2:30 AM               3:00 AM      0
3:00 AM               3:30 AM      0
3:30 AM                4:00 AM      1
4:00 AM               4:30 AM      0
4:30 AM               5:00 AM      1
5:00 AM               5:30 AM      0
5:30 AM               6:00 AM      0
6:00 AM               6:30 AM      0
6:30 AM               7:00 AM      1
7:00 AM               7:30 AM      0
7:30 AM               8:00 AM      1
8:00 AM               8:30 AM      0
8:30 AM               9:00 AM      1
9:00 AM               9:30 AM      2
9:30 AM             10:00 AM      7
10:00 AM      10:30 AM      1
10:30 AM      11:00 AM      3
11:00 AM      11:30 AM      2
11:30 AM      12:00 PM      2
12:00 PM      12:30 PM      5
12:30 PM      1:00 PM              1
1:00 PM                1:30 PM      1
1:30 PM                 2:00 PM      1
2:00 PM                2:30 PM      2
2:30 PM                3:00 PM      3
3:00 PM                3:30 PM      3
3:30 PM                4:00 PM      5
4:00 PM                4:30 PM      1
4:30 PM                5:00 PM      1
5:00 PM               5:30 PM             2
5:30 PM               6:00 PM        3
6:00 PM               6:30 PM        2
6:30 PM               7:00 PM             2
7:00 PM              7:30 PM             5
7:30 PM              8:00 PM             5
8:00 PM              8:30 PM             2
8:30 PM              9:00 PM             4
9:00 PM              9:30 PM         7
9:30 PM             10:00 PM        1
10:00 PM      10:30 PM      4
10:30 PM      11:00 PM      5
11:00 PM      11:30 PM      6
11:30 PM      12:00 AM      5

I would like the input to be as large as possible, but the solution needs to be able to handle inputs as large as 130 rows. I am using Excel 2013.

Is this possible?

Thanks, Richard
Input.xlsx
Output.xlsx
fever_rcaTechnician Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

abbas abdullaCommented:
Hi,

Use Sumproduct function
=SUMPRODUCT(--(MOD($A$1:$A$2000,1)>FromTime),--(MOD($A$1:$A$2000,1)<=ToTime))

See the attached file
Input.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try this....
The formula is similar to what Abbas has suggested.
But you can also construct the time brackets as shown in the attached.

=SUMPRODUCT((MOD($A$1:$A$103,1)>=C1)*(MOD($A$1:$A$103,1)<=D1))

Open in new window

Input.xlsx
Rgonzo1971Commented:
HI,

WARNING

one of the comparison cannot be = because you could count something twice if for example at 8:00:00
then try
=SUMPRODUCT((MOD($A$1:$A$103,1)>=C1)*(MOD($A$1:$A$103,1)<D1))

Open in new window

Regards
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Good point Rgonzo!
aikimarkCommented:
I would add a new column that blocks (encodes) the time into half-hour intervals.
=HOUR(A1)&":"&INT(MINUTE(A1)/30)*30

Open in new window

The counting should be simple after that.
Rob HensonFinance AnalystCommented:
The sample only shows time stamps from one day. Will you have multiple dates and need to specify the date element as well?

You can also use the COUNTIFS function:

=COUNTIFS(A:A,">="&$H$1+E2,A:A,"<="&$H$1+F2)

H1 = Date
E2 = lower boundary (for further rows this is previous upper boundary plus 1 second)
F2 = Upper boundary (lower boundary plus 30 minutes)

Your time values are to accuracy in seconds. I have done >=  & <= for both criteria because I have set the lower and upper boundaries differently, the lower boundary of one pair is one second later that the upper boundary of the previous pair so there won't be duplicates as suggested earlier.

See attached.
Input.xlsx
aikimarkCommented:
You can also use the Time() function:
=TIME(HOUR(A1), INT(MINUTE(A1)/30)*30, 0)

Open in new window

fever_rcaTechnician Author Commented:
Wow! Thanks all. I will run a few checks and let everyone know.

Rob Henson - I am doing a 7 day period, but I get the data one day at a time. Not sure if performing the calculation 7 days at a shot would be helpful or not. I'll look into it.
fever_rcaTechnician Author Commented:
Thanks all! Abbas' solution does exactly what I wanted.
abbas abdullaCommented:
Hi,

You are welcomed, glad to help. The formula will fail only to count one record of the day if time is 0:0:0 - check the attached file the count returns 1800 count for all day interval which is representing the number of seconds in each 30 minutes but it return 1799 for the first interval because it fail to count the 0:00 time with the result.
Input.xlsx
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.