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

fever_rca
fever_rca used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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
Top Expert 2016

Commented:
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
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Good point Rgonzo!
Top Expert 2014

Commented:
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 Analyst

Commented:
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
Top Expert 2014

Commented:
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.
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial