Counting records based on date and time.

[bandera]

Hello,

Here is a column from a table containint Help Desk tickets:

Open Date
06/24/2013 0:10
06/24/2013 7:32
06/24/2013 7:46
06/24/2013 16:44
06/24/2013 16:44
06/24/2013 16:55
06/24/2013 16:58
06/24/2013 16:59
06/24/2013 16:59
06/24/2013 17:08
06/24/2013 17:10
06/24/2013 17:41
06/24/2013 17:46
06/24/2013 17:55
06/24/2013 18:47
06/24/2013 19:57
06/24/2013 21:54
06/25/2013 6:54
06/25/2013 7:13
06/25/2013 7:24

I will like to count only those opened between 05:00 PM and 07:00 AM next day.

Which formula i can use?

Thank you for your help.

[zbor]

Assuming your data is in D column:

Please Login or Register  to view this content.

[zbor]

Hint: User >= and <= if including 5:00 and 7:00

[bandera]

Thank you zbor, however the count is 0. Should the first "TODAY" be replaced with yestarday?

[zbor]

Of course, if you use prior days it will count 0.

Try:

Please Login or Register  to view this content.

[bandera]

Getting there. Thank you for your reply.
Here is my entire column. When plugged the formula it counts 7 records when there should be 9 records.

Open Date
06/24/2013 0:10
06/24/2013 7:32
06/24/2013 7:46
06/24/2013 7:49
06/24/2013 7:55
06/24/2013 8:00
06/24/2013 8:04
06/24/2013 8:05
06/24/2013 8:06
06/24/2013 8:10
06/24/2013 8:11
06/24/2013 8:17
06/24/2013 8:20
06/24/2013 8:23
06/24/2013 8:26
06/24/2013 8:27
06/24/2013 8:31
06/24/2013 8:33
06/24/2013 8:33
06/24/2013 8:34
06/24/2013 8:42
06/24/2013 8:47
06/24/2013 8:49
06/24/2013 8:50
06/24/2013 8:51
06/24/2013 8:52
06/24/2013 8:52
06/24/2013 8:55
06/24/2013 8:58
06/24/2013 9:02
06/24/2013 9:03
06/24/2013 9:03
06/24/2013 9:03
06/24/2013 9:04
06/24/2013 9:09
06/24/2013 9:11
06/24/2013 9:15
06/24/2013 9:16
06/24/2013 9:18
06/24/2013 9:23
06/24/2013 9:24
06/24/2013 9:24
06/24/2013 9:25
06/24/2013 9:26
06/24/2013 9:27
06/24/2013 9:29
06/24/2013 9:29
06/24/2013 9:30
06/24/2013 9:32
06/24/2013 9:34
06/24/2013 9:35
06/24/2013 9:37
06/24/2013 9:39
06/24/2013 9:40
06/24/2013 9:40
06/24/2013 9:43
06/24/2013 9:46
06/24/2013 9:46
06/24/2013 9:49
06/24/2013 9:50
06/24/2013 9:51
06/24/2013 9:52
06/24/2013 9:52
06/24/2013 9:54
06/24/2013 9:55
06/24/2013 9:57
06/24/2013 9:58
06/24/2013 10:04
06/24/2013 10:04
06/24/2013 10:07
06/24/2013 10:07
06/24/2013 10:11
06/24/2013 10:13
06/24/2013 10:13
06/24/2013 10:14
06/24/2013 10:14
06/24/2013 10:15
06/24/2013 10:17
06/24/2013 10:20
06/24/2013 10:20
06/24/2013 10:23
06/24/2013 10:23
06/24/2013 10:23
06/24/2013 10:24
06/24/2013 10:26
06/24/2013 10:27
06/24/2013 10:28
06/24/2013 10:30
06/24/2013 10:41
06/24/2013 10:41
06/24/2013 10:41
06/24/2013 10:41
06/24/2013 10:47
06/24/2013 10:50
06/24/2013 10:53
06/24/2013 10:54
06/24/2013 10:56
06/24/2013 10:57
06/24/2013 10:57
06/24/2013 10:59
06/24/2013 10:59
06/24/2013 11:00
06/24/2013 11:07
06/24/2013 11:13
06/24/2013 11:18
06/24/2013 11:18
06/24/2013 11:19
06/24/2013 11:21
06/24/2013 11:24
06/24/2013 11:27
06/24/2013 11:29
06/24/2013 11:30
06/24/2013 11:30
06/24/2013 11:33
06/24/2013 11:33
06/24/2013 11:37
06/24/2013 11:39
06/24/2013 11:40
06/24/2013 11:40
06/24/2013 11:43
06/24/2013 11:44
06/24/2013 11:54
06/24/2013 12:03
06/24/2013 12:07
06/24/2013 12:07
06/24/2013 12:20
06/24/2013 12:21
06/24/2013 12:23
06/24/2013 12:24
06/24/2013 12:26
06/24/2013 12:26
06/24/2013 12:26
06/24/2013 12:27
06/24/2013 12:30
06/24/2013 12:31
06/24/2013 12:32
06/24/2013 12:34
06/24/2013 12:34
06/24/2013 12:38
06/24/2013 12:40
06/24/2013 12:40
06/24/2013 12:41
06/24/2013 12:47
06/24/2013 12:49
06/24/2013 12:50
06/24/2013 12:53
06/24/2013 12:53
06/24/2013 12:55
06/24/2013 12:56
06/24/2013 12:56
06/24/2013 12:56
06/24/2013 12:56
06/24/2013 12:58
06/24/2013 13:00
06/24/2013 13:02
06/24/2013 13:08
06/24/2013 13:15
06/24/2013 13:18
06/24/2013 13:19
06/24/2013 13:23
06/24/2013 13:24
06/24/2013 13:30
06/24/2013 13:33
06/24/2013 13:33
06/24/2013 13:38
06/24/2013 13:40
06/24/2013 13:45
06/24/2013 14:01
06/24/2013 14:06
06/24/2013 14:07
06/24/2013 14:07
06/24/2013 14:09
06/24/2013 14:09
06/24/2013 14:09
06/24/2013 14:10
06/24/2013 14:10
06/24/2013 14:10
06/24/2013 14:13
06/24/2013 14:16
06/24/2013 14:16
06/24/2013 14:19
06/24/2013 14:21
06/24/2013 14:22
06/24/2013 14:22
06/24/2013 14:23
06/24/2013 14:23
06/24/2013 14:23
06/24/2013 14:24
06/24/2013 14:28
06/24/2013 14:32
06/24/2013 14:34
06/24/2013 14:37
06/24/2013 14:38
06/24/2013 14:38
06/24/2013 14:40
06/24/2013 14:41
06/24/2013 14:42
06/24/2013 14:45
06/24/2013 14:45
06/24/2013 14:47
06/24/2013 14:48
06/24/2013 14:53
06/24/2013 14:53
06/24/2013 15:15
06/24/2013 15:18
06/24/2013 15:22
06/24/2013 15:24
06/24/2013 15:25
06/24/2013 15:26
06/24/2013 15:29
06/24/2013 15:34
06/24/2013 15:38
06/24/2013 15:38
06/24/2013 15:45
06/24/2013 15:47
06/24/2013 15:48
06/24/2013 15:49
06/24/2013 15:53
06/24/2013 15:58
06/24/2013 15:58
06/24/2013 15:58
06/24/2013 16:00
06/24/2013 16:01
06/24/2013 16:01
06/24/2013 16:03
06/24/2013 16:07
06/24/2013 16:11
06/24/2013 16:14
06/24/2013 16:17
06/24/2013 16:18
06/24/2013 16:19
06/24/2013 16:22
06/24/2013 16:25
06/24/2013 16:29
06/24/2013 16:32
06/24/2013 16:34
06/24/2013 16:35
06/24/2013 16:40
06/24/2013 16:44
06/24/2013 16:44
06/24/2013 16:55
06/24/2013 16:58
06/24/2013 16:59
06/24/2013 16:59
06/24/2013 17:08
06/24/2013 17:10
06/24/2013 17:41
06/24/2013 17:46
06/24/2013 17:55
06/24/2013 18:47
06/24/2013 19:57
06/24/2013 21:54
06/25/2013 6:54
06/25/2013 7:13
06/25/2013 7:24
06/25/2013 7:34
06/25/2013 7:47
06/25/2013 7:49
06/25/2013 7:53
06/25/2013 7:56
06/25/2013 7:59
06/25/2013 8:05
06/25/2013 8:06
06/25/2013 8:09
06/25/2013 8:13
06/25/2013 8:15
06/25/2013 8:19
06/25/2013 8:23
06/25/2013 8:30
06/25/2013 8:39
06/25/2013 8:41
06/25/2013 8:42
06/25/2013 8:44
06/25/2013 8:45
06/25/2013 8:48
06/25/2013 8:54
06/25/2013 8:54
06/25/2013 8:57
06/25/2013 8:58
06/25/2013 9:01
06/25/2013 9:03
06/25/2013 9:06
06/25/2013 9:07
06/25/2013 9:10
06/25/2013 9:11
06/25/2013 9:11
06/25/2013 9:22
06/25/2013 9:25
06/25/2013 9:28
06/25/2013 9:31
06/25/2013 9:34
06/25/2013 9:35
06/25/2013 9:35
06/25/2013 9:38
06/25/2013 9:42
06/25/2013 9:43
06/25/2013 9:46
06/25/2013 9:49
06/25/2013 9:51
06/25/2013 9:51
06/25/2013 9:58
06/25/2013 10:01
06/25/2013 10:11
06/25/2013 10:17
06/25/2013 10:17
06/25/2013 10:22
06/25/2013 10:23
06/25/2013 10:23
06/25/2013 10:26
06/25/2013 10:28
06/25/2013 10:31
06/25/2013 10:32
06/25/2013 10:35
06/25/2013 10:36
06/25/2013 10:36
06/25/2013 10:39
06/25/2013 10:40
06/25/2013 10:44
06/25/2013 10:45
06/25/2013 10:47
06/25/2013 10:48
06/25/2013 10:59
06/25/2013 11:02
06/25/2013 11:04
06/25/2013 11:05
06/25/2013 11:06
06/25/2013 11:07
06/25/2013 11:10
06/25/2013 11:12
06/25/2013 11:12
06/25/2013 11:13
06/25/2013 11:17
06/25/2013 11:18
06/25/2013 11:25
06/25/2013 11:34
06/25/2013 11:36
06/25/2013 11:37
06/25/2013 11:43
06/25/2013 11:46
06/25/2013 11:51
06/25/2013 11:52
06/25/2013 11:53
06/25/2013 12:00
06/25/2013 12:01
06/25/2013 12:06
06/25/2013 12:08
06/25/2013 12:09
06/25/2013 12:11
06/25/2013 12:16

=SUMPRODUCT(--($N$2:$N$400>$N$2+"17:00"),--($N$2:$N$400<$N$2+"7:00"+1))

Appreciate your feedback.

[zbor]

My mistake.. Try this:

Please Login or Register  to view this content.

[bandera]

Thank you so much. The formula works perfect.

How can I count the column N from Sheet1 of the excel workbook?

[zbor]

With COUNT formula

[bandera]

Thank you zbor for your help.
Here is what I was looking for:

Please Login or Register  to view this content.

[zbor]

You have extra sheet1 in formula.. This should work too:

Please Login or Register  to view this content.

Hint: You don't need to guess that. Copy formula on sheet 2. Click on it to edit it. Then for each range delete it (For example delete =SUMPRODUCT(--($N$2:$N$400>INT($N$2)+"17:00"),--($N$2:$N$400<INT($N$2)+"7:00"+1)) red in formula) and then click on sheet1 and select this range. That way you can edit formula with other range.