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
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
Microsoft ExcelMicrosoft Office
Last Comment
abbas abdulla
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
HI,
WARNING
one of the comparison cannot be = because you could count something twice if for example at 8:00:00
then try
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))
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
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
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 COUNTIFS function:
=COUNTIFS(A:A,">="&$H$1+E2
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)
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.
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.
ASKER
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
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
The formula is similar to what Abbas has suggested.
But you can also construct the time brackets as shown in the attached.
Open in new window
Input.xlsx