Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

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

Avatar of fever_rca
fever_rcaFlag for United States of America asked on
Microsoft OfficeMicrosoft Excel
10 Comments1 Solution106 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Avatar of abbas abdulla
abbas abdullaFlag of Bahrain image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 10 Comments.
See Answers