Count of time slot

[Sri_33]

Book1
	A	B	C	D	E
1	Time		Start	End	Count
2	13:30		13:00	13:29	3
3	13:00
4	12:30
5	14:00
6	13:00
7	13:30
8	13:00
9	13:30
10	14:00
11	15:00
12	14:30
13	12:30
14	14:00
15	13:30
16	15:00
17	14:00
Sheet1

Hello all, I want the count of the time slots from column A which are in between(C2 & D2 Cells) to be projected in E2 cell, please help me with the formula.

 

[Eric W]

Try:

Book2
	A	B	C	D	E
1	Time		Start	End	Count
2	1:30:00 PM		1:00:00 PM	1:29:00 PM	3
3	1:00:00 PM
4	12:30:00 PM
5	2:00:00 PM
6	1:00:00 PM
7	1:30:00 PM
8	1:00:00 PM
9	1:30:00 PM
10	2:00:00 PM
11	3:00:00 PM
12	2:30:00 PM
13	12:30:00 PM
14	2:00:00 PM
15	1:30:00 PM
16	3:00:00 PM
17	2:00:00 PM
Sheet13

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS(A2:A20,">="&C2,A2:A20,"<="&D2)

 

[Sri_33]

Try:

Book2
	A	B	C	D	E
1	Time		Start	End	Count
2	1:30:00 PM		1:00:00 PM	1:29:00 PM	3
3	1:00:00 PM
4	12:30:00 PM
5	2:00:00 PM
6	1:00:00 PM
7	1:30:00 PM
8	1:00:00 PM
9	1:30:00 PM
10	2:00:00 PM
11	3:00:00 PM
12	2:30:00 PM
13	12:30:00 PM
14	2:00:00 PM
15	1:30:00 PM
16	3:00:00 PM
17	2:00:00 PM
Sheet13

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS(A2:A20,">="&C2,A2:A20,"<="&D2)

it is working thank you

 

[Eric W]

Happy to help, thanks for the update. ?

 

[Sri_33]

Breaks TC.xlsx
	A	B	C	D	E	F
1	Meal	M1-End
2	0:00	0:15
3	0:00	0:15
4	0:00	0:15		0:00	0:15	47
5	0:00	0:15
6	0:00	0:15
7	0:00	0:15
8	0:00	0:15
9	0:00	0:15
10	0:00	0:15
11	0:00	0:15
12	0:00	0:15
13	0:00	0:15
14	0:00	0:15
15	0:00	0:15
16	0:00	0:15
17	0:00	0:15
18	0:00	0:15
19	0:00	0:15
20	0:00	0:15
21	0:00	0:15
22	0:00	0:15
23	0:00	0:15
24	0:00	0:15
25	0:00	0:15
26	0:00	0:15
27	0:00	0:15
28	0:00	0:15
29	0:00	0:15
30	0:00	0:15
31	0:00	0:15
32	0:00	0:15
33	0:00	0:15
34	0:00	0:15
35	0:00	0:15
36	0:00	0:15
37	0:00	0:15
38	0:00	0:15
39	0:00	0:15
40	0:00	0:15
41	0:00	0:15
42	0:00	0:15
43	0:00	0:15
44	0:00	0:15
45	0:00	0:15
46	0:00	0:15
47	0:00	0:15
48	0:00	0:15
49	0:00	0:15
50	0:00	0:15
51	0:00	0:15
52	0:00	0:15
53	0:00	0:15
54	0:00	0:15
55	0:00	0:15
56	0:00	0:15
57	0:00	0:15
58	0:00	0:15
59	0:00	0:15
60	0:00	0:15
61	0:00	0:15
62	0:00	0:15
63	0:00	0:15
64	0:00	0:15
65	0:00	0:15
66	0:00	0:15
67	0:00	0:15
68	0:00	0:15
69	0:00	0:15
70	0:00	0:15
71	0:00	0:15
72	0:00	0:15
73	0:00	0:15
74	0:00	0:15
75	0:00	0:15
76	0:00	0:15
77	0:00	0:15
78	0:00	0:15
79	0:00	0:15
80	0:00	0:15
81	0:00	0:15
82	0:00	0:15
83	0:00	0:15
84	0:00	0:15
85	0:00	0:15
86	0:00	0:15
87	0:00	0:15
88	0:00	0:15
89	0:00	0:15
90	0:00	0:15
91	0:00	0:15
92	0:00	0:15
93	0:00	0:15
94	0:00	0:15
95	0:00	0:15
96	0:00	0:15
97	0:00	0:15
98	0:00	0:15
99	0:00	0:15
100	0:00	0:15
101	0:00	0:15
102	0:00	0:15
103	0:00	0:15
104	0:00	0:15
105	0:00	0:15
106	0:00	0:15
107	0:00	0:15
108	0:00	0:15
109	0:00	0:15
110	0:00	0:15
111	0:00	0:15
112	0:00	0:15
Sheet4

Cell Formulas
Range	Formula
F4	F4	=COUNTIFS(A:A,"="&D4,B:B,"="&E4)

the count should be coming as 111 in F4 cell, but it is showing as 47, please help me in this.

 

[Sri_33]

Happy to help, thanks for the update. ?

Hi Eric, Kindly help me with the below issue.

 

[Eric W]

You have the values in columns A:B formatted as times, so that's all you see. But if you change the format to General, and show more decimal places, this is what you see:

Book1
	A	B	C	D	E	F
1	Meal	M1-End
2	0.00000000	0.010417
3	0.00000000	0.010417
4	1.00000000	1.010417		0	0.01041666666667	47
5	1.00000000	1.010417				111
6	1.00000000	1.010417
7	1.00000000	1.010417
8	0.00000000	0.010417
9	0.00000000	0.010417
10	1.00000000	1.010417
11	1.00000000	1.010417
12	0.00000000	0.010417
13	1.00000000	1.010417
14	0.00000000	0.010417
15	0.00000000	0.010417
16	1.00000000	1.010417
17	1.00000000	1.010417
18	1.00000000	1.010417
19	1.00000000	1.010417
20	1.00000000	1.010417
21	1.00000000	1.010417
22	1.00000000	1.010417
23	1.00000000	1.010417
24	0.00000000	0.010417
25	1.00000000	1.010417
26	1.00000000	1.010417
27	1.00000000	1.010417
28	0.00000000	0.010417
29	1.00000000	1.010417
30	1.00000000	1.010417
31	1.00000000	1.010417
32	0.00000000	0.010417
33	1.00000000	1.010417
34	0.00000000	0.010417
35	0.00000000	0.010417
36	0.00000000	0.010417
37	0.00000000	0.010417
38	0.00000000	0.010417
39	1.00000000	1.010417
40	1.00000000	1.010417
41	0.00000000	0.010417
42	1.00000000	1.010417
43	1.00000000	1.010417
44	0.00000000	0.010417
45	1.00000000	1.010417
46	0.00000000	0.010417
47	1.00000000	1.010417
48	1.00000000	1.010417
49	1.00000000	1.010417
50	0.00000000	0.010417
51	1.00000000	1.010417
52	1.00000000	1.010417
53	0.00000000	0.010417
54	0.00000000	0.010417
55	1.00000000	1.010417
56	0.00000000	0.010417
57	1.00000000	1.010417
58	1.00000000	1.010417
59	0.00000000	0.010417
60	1.00000000	1.010417
61	1.00000000	1.010417
62	0.00000000	0.010417
63	1.00000000	1.010417
64	0.00000000	0.010417
65	0.00000000	0.010417
66	1.00000000	1.010417
67	1.00000000	1.010417
68	0.00000000	0.010417
69	1.00000000	1.010417
70	1.00000000	1.010417
71	0.00000000	0.010417
72	1.00000000	1.010417
73	1.00000000	1.010417
74	0.00000000	0.010417
75	0.00000000	0.010417
76	1.00000000	1.010417
77	1.00000000	1.010417
78	0.00000000	0.010417
79	1.00000000	1.010417
80	1.00000000	1.010417
81	1.00000000	1.010417
82	0.00000000	0.010417
83	1.00000000	1.010417
84	1.00000000	1.010417
85	0.00000000	0.010417
86	0.00000000	0.010417
87	1.00000000	1.010417
88	1.00000000	1.010417
89	1.00000000	1.010417
90	0.00000000	0.010417
91	0.00000000	0.010417
92	0.00000000	0.010417
93	1.00000000	1.010417
94	0.00000000	0.010417
95	0.00000000	0.010417
96	1.00000000	1.010417
97	1.00000000	1.010417
98	0.00000000	0.010417
99	1.00000000	1.010417
100	1.00000000	1.010417
101	0.00000000	0.010417
102	1.00000000	1.010417
103	0.00000000	0.010417
104	0.00000000	0.010417
105	0.00000000	0.010417
106	1.00000000	1.010417
107	1.00000000	1.010417
108	1.00000000	1.010417
109	1.00000000	1.010417
110	0.00000000	0.010417
111	0.00000000	0.010417
112	0.00000000	0.010417
113
114
Sheet3

Cell Formulas
Range	Formula
F4	F4	=COUNTIFS(A:A,"="&D4,B:B,"="&E4)
F5	F5	=SUMPRODUCT(--(MOD(A2:A112,1)<=MOD(D4,1)),--(MOD(A2:A112,1)<=MOD(E4,1)))

A date is a number (the number of days since January 1, 1900), and a time is a fraction of a day. So looking at these times, some of them start with a 1, meaning the next day. These are the ones that are not counted. You don't see the 1 because of how the cell is formatted. To clear it up, you need to remove the leading 1s.

It's also worth noting that since times are fractions of a day, and fractions cannot be stored exactly in Excel, sometimes you get odd mismatches as well. You might want to change the E4 cell to 00:15:01.

If you do not care about the leading 1s, and you just want to compare the time portions, try the formula in F5.

Good luck!

 

[Sri_33]

You have the values in columns A:B formatted as times, so that's all you see. But if you change the format to General, and show more decimal places, this is what you see:

Book1
	A	B	C	D	E	F
1	Meal	M1-End
2	0.00000000	0.010417
3	0.00000000	0.010417
4	1.00000000	1.010417		0	0.01041666666667	47
5	1.00000000	1.010417				111
6	1.00000000	1.010417
7	1.00000000	1.010417
8	0.00000000	0.010417
9	0.00000000	0.010417
10	1.00000000	1.010417
11	1.00000000	1.010417
12	0.00000000	0.010417
13	1.00000000	1.010417
14	0.00000000	0.010417
15	0.00000000	0.010417
16	1.00000000	1.010417
17	1.00000000	1.010417
18	1.00000000	1.010417
19	1.00000000	1.010417
20	1.00000000	1.010417
21	1.00000000	1.010417
22	1.00000000	1.010417
23	1.00000000	1.010417
24	0.00000000	0.010417
25	1.00000000	1.010417
26	1.00000000	1.010417
27	1.00000000	1.010417
28	0.00000000	0.010417
29	1.00000000	1.010417
30	1.00000000	1.010417
31	1.00000000	1.010417
32	0.00000000	0.010417
33	1.00000000	1.010417
34	0.00000000	0.010417
35	0.00000000	0.010417
36	0.00000000	0.010417
37	0.00000000	0.010417
38	0.00000000	0.010417
39	1.00000000	1.010417
40	1.00000000	1.010417
41	0.00000000	0.010417
42	1.00000000	1.010417
43	1.00000000	1.010417
44	0.00000000	0.010417
45	1.00000000	1.010417
46	0.00000000	0.010417
47	1.00000000	1.010417
48	1.00000000	1.010417
49	1.00000000	1.010417
50	0.00000000	0.010417
51	1.00000000	1.010417
52	1.00000000	1.010417
53	0.00000000	0.010417
54	0.00000000	0.010417
55	1.00000000	1.010417
56	0.00000000	0.010417
57	1.00000000	1.010417
58	1.00000000	1.010417
59	0.00000000	0.010417
60	1.00000000	1.010417
61	1.00000000	1.010417
62	0.00000000	0.010417
63	1.00000000	1.010417
64	0.00000000	0.010417
65	0.00000000	0.010417
66	1.00000000	1.010417
67	1.00000000	1.010417
68	0.00000000	0.010417
69	1.00000000	1.010417
70	1.00000000	1.010417
71	0.00000000	0.010417
72	1.00000000	1.010417
73	1.00000000	1.010417
74	0.00000000	0.010417
75	0.00000000	0.010417
76	1.00000000	1.010417
77	1.00000000	1.010417
78	0.00000000	0.010417
79	1.00000000	1.010417
80	1.00000000	1.010417
81	1.00000000	1.010417
82	0.00000000	0.010417
83	1.00000000	1.010417
84	1.00000000	1.010417
85	0.00000000	0.010417
86	0.00000000	0.010417
87	1.00000000	1.010417
88	1.00000000	1.010417
89	1.00000000	1.010417
90	0.00000000	0.010417
91	0.00000000	0.010417
92	0.00000000	0.010417
93	1.00000000	1.010417
94	0.00000000	0.010417
95	0.00000000	0.010417
96	1.00000000	1.010417
97	1.00000000	1.010417
98	0.00000000	0.010417
99	1.00000000	1.010417
100	1.00000000	1.010417
101	0.00000000	0.010417
102	1.00000000	1.010417
103	0.00000000	0.010417
104	0.00000000	0.010417
105	0.00000000	0.010417
106	1.00000000	1.010417
107	1.00000000	1.010417
108	1.00000000	1.010417
109	1.00000000	1.010417
110	0.00000000	0.010417
111	0.00000000	0.010417
112	0.00000000	0.010417
113
114
Sheet3

Cell Formulas
Range	Formula
F4	F4	=COUNTIFS(A:A,"="&D4,B:B,"="&E4)
F5	F5	=SUMPRODUCT(--(MOD(A2:A112,1)<=MOD(D4,1)),--(MOD(A2:A112,1)<=MOD(E4,1)))

A date is a number (the number of days since January 1, 1900), and a time is a fraction of a day. So looking at these times, some of them start with a 1, meaning the next day. These are the ones that are not counted. You don't see the 1 because of how the cell is formatted. To clear it up, you need to remove the leading 1s.

It's also worth noting that since times are fractions of a day, and fractions cannot be stored exactly in Excel, sometimes you get odd mismatches as well. You might want to change the E4 cell to 00:15:01.

If you do not care about the leading 1s, and you just want to compare the time portions, try the formula in F5.

Good luck!

Hello Eric, I have few doubts here,
1) Which format the above mentioned cells(A & B columns) are in, because when Iam trying in the date formated cells formula mentioned in F5 cell is not giving the correct output.
2) I want to apply other conditions as well( location, shift etc mentioned in the screenshot for reference) can I add these conditions in Sumproduct function?
3) Is there any other way to only get the time values?

 

[Eric W]

1) Based on your example, the timestamps in column B have a 1 or a 0 on it based on if column A has a 1 or a 0. If so, we can just subtract column A from column B to get a time. It looks like columns A:B are being populated by some other software, so we need to understand what it's giving us. As far as formatting column B, I'd recommend using the usual time format (which I did on the lower half of my table below).

2) Yes, we can easily add other conditions, see below.

3) I'm getting the time values only by subtracting column A. It seems to work, but on your whole data set, make sure that subtracting column A is right. But are you sure you want the timestamps? Do you want to include column A as a condition, in other words, you want to make sure column A is a 1 before you count it?

Book2
	A	B	C	D	E	F	G	H	I	J
1	Meal	M1-End	Shift	Location		Start	End	Shift	Location	Count
2	0	0.01041667	Morning	BS		0:00	0:15	Morning	TP	4
3	0	0.01041667	Morning	TP
4	1	1.01041667	Morning	TP
5	1	1.01041667	Morning	TP
6	1	1.01041667	Morning	TP
7	1	1.01041667	Morning	BS
8	0	0.01041667	Morning	BS
9	0	0.01041667	Mid	KL
10	1	0:15	Mid	KL
11	1	0:15	Mid	KL
12	0	0:15	Mid	KL
13	1	0:15	Mid	KL
14	0	0:15	Morning	BS
15	0	0:15	Morning	BS
16	1	0:15	Night	BS
17
Sheet13

Cell Formulas
Range	Formula
J2	J2	=SUMPRODUCT(--(B2:B16-A2:A16>=F2),--(B2:B16-A2:A16<=G2),--(C2:C16=H2),--(D2:D16=I2))