Link to home
Start Free TrialLog in
Avatar of fever_rca
fever_rcaFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of abbas abdulla
abbas abdulla
Flag of Bahrain image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of Rgonzo1971
Rgonzo1971

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
Good point Rgonzo!
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.
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
You can also use the Time() function:
=TIME(HOUR(A1), INT(MINUTE(A1)/30)*30, 0)

Open in new window

Avatar of fever_rca

ASKER

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