Return Value for Day of Week Interval based on Start / Stop time

[Marklarbear]

Hi Brains trust,

I'm looking to build a formula in column E to do the following in the bottom table:

Look at the Day of week in column A (bottom table)
Find that day in the top table
Look at the time interval in column C (bottom table)
Is this time interval between the start / stop time for the corrosponding day in the top table
If yes - return 'staff per int' value
If no - leave cell blank

I've tried a combination of 'IF' + 'If(AND' + 'XLOOKUP' statements but I just can't get it right....

Book1
	A	B	C	D	E
1	Day	Start	Stop	Staff Per Int
2	Mon	08:00 AM	08:00 PM	20
3	Tue	08:00 AM	08:00 PM	20
4	Wed	08:00 AM	08:00 PM	20
5	Thu	08:00 AM	08:00 PM	20
6	Fri	08:00 AM	08:00 PM	20
7	Sat	09:00 AM	06:00 PM	5
8	Sun
9
10	Start Date Of Cycle:			21/10/2022
11
12
13	Fri	21/10/2022	12:00 AM	00:15
14	Fri	21/10/2022	12:15 AM	00:15
15	Fri	21/10/2022	12:30 AM	00:15
16	Fri	21/10/2022	12:45 AM	00:15
17	Fri	21/10/2022	01:00 AM	00:15
18	Fri	21/10/2022	01:15 AM	00:15
19	Fri	21/10/2022	01:30 AM	00:15
20	Fri	21/10/2022	01:45 AM	00:15
21	Fri	21/10/2022	02:00 AM	00:15
22	Fri	21/10/2022	02:15 AM	00:15
23	Fri	21/10/2022	02:30 AM	00:15
24	Fri	21/10/2022	02:45 AM	00:15
25	Fri	21/10/2022	03:00 AM	00:15
26	Fri	21/10/2022	03:15 AM	00:15
27	Fri	21/10/2022	03:30 AM	00:15
28	Fri	21/10/2022	03:45 AM	00:15
29	Fri	21/10/2022	04:00 AM	00:15
30	Fri	21/10/2022	04:15 AM	00:15
31	Fri	21/10/2022	04:30 AM	00:15
32	Fri	21/10/2022	04:45 AM	00:15
33	Fri	21/10/2022	05:00 AM	00:15
34	Fri	21/10/2022	05:15 AM	00:15
35	Fri	21/10/2022	05:30 AM	00:15
36	Fri	21/10/2022	05:45 AM	00:15
37	Fri	21/10/2022	06:00 AM	00:15
38	Fri	21/10/2022	06:15 AM	00:15
39	Fri	21/10/2022	06:30 AM	00:15
40	Fri	21/10/2022	06:45 AM	00:15
41	Fri	21/10/2022	07:00 AM	00:15
42	Fri	21/10/2022	07:15 AM	00:15
43	Fri	21/10/2022	07:30 AM	00:15
44	Fri	21/10/2022	07:45 AM	00:15
45	Fri	21/10/2022	08:00 AM	00:15	20
46	Fri	21/10/2022	08:15 AM	00:15	20
47	Fri	21/10/2022	08:30 AM	00:15	20
48	Fri	21/10/2022	08:45 AM	00:15	20
49	Fri	21/10/2022	09:00 AM	00:15	20
50	Fri	21/10/2022	09:15 AM	00:15	20
51	Fri	21/10/2022	09:30 AM	00:15	20
52	Fri	21/10/2022	09:45 AM	00:15	20
53	Fri	21/10/2022	10:00 AM	00:15	20
54	Fri	21/10/2022	10:15 AM	00:15	20
55	Fri	21/10/2022	10:30 AM	00:15	20
56	Fri	21/10/2022	10:45 AM	00:15	20
57	Fri	21/10/2022	11:00 AM	00:15	20
58	Fri	21/10/2022	11:15 AM	00:15	20
59	Fri	21/10/2022	11:30 AM	00:15	20
60	Fri	21/10/2022	11:45 AM	00:15	20
61	Fri	21/10/2022	12:00 PM	00:15	20
62	Fri	21/10/2022	12:15 PM	00:15	20
63	Fri	21/10/2022	12:30 PM	00:15	20
64	Fri	21/10/2022	12:45 PM	00:15	20
65	Fri	21/10/2022	01:00 PM	00:15	20
66	Fri	21/10/2022	01:15 PM	00:15	20
67	Fri	21/10/2022	01:30 PM	00:15	20
68	Fri	21/10/2022	01:45 PM	00:15	20
69	Fri	21/10/2022	02:00 PM	00:15	20
70	Fri	21/10/2022	02:15 PM	00:15	20
71	Fri	21/10/2022	02:30 PM	00:15	20
72	Fri	21/10/2022	02:45 PM	00:15	20
73	Fri	21/10/2022	03:00 PM	00:15	20
74	Fri	21/10/2022	03:15 PM	00:15	20
75	Fri	21/10/2022	03:30 PM	00:15	20
76	Fri	21/10/2022	03:45 PM	00:15	20
77	Fri	21/10/2022	04:00 PM	00:15	20
78	Fri	21/10/2022	04:15 PM	00:15	20
79	Fri	21/10/2022	04:30 PM	00:15	20
80	Fri	21/10/2022	04:45 PM	00:15	20
81	Fri	21/10/2022	05:00 PM	00:15	20
82	Fri	21/10/2022	05:15 PM	00:15	20
83	Fri	21/10/2022	05:30 PM	00:15	20
84	Fri	21/10/2022	05:45 PM	00:15	20
85	Fri	21/10/2022	06:00 PM	00:15	20
86	Fri	21/10/2022	06:15 PM	00:15	20
87	Fri	21/10/2022	06:30 PM	00:15	20
88	Fri	21/10/2022	06:45 PM	00:15	20
89	Fri	21/10/2022	07:00 PM	00:15	20
90	Fri	21/10/2022	07:15 PM	00:15	20
91	Fri	21/10/2022	07:30 PM	00:15	20
92	Fri	21/10/2022	07:45 PM	00:15	20
93	Fri	21/10/2022	08:00 PM	00:15
94	Fri	21/10/2022	08:15 PM	00:15
95	Fri	21/10/2022	08:30 PM	00:15
96	Fri	21/10/2022	08:45 PM	00:15
97	Fri	21/10/2022	09:00 PM	00:15
98	Fri	21/10/2022	09:15 PM	00:15
99	Fri	21/10/2022	09:30 PM	00:15
100	Fri	21/10/2022	09:45 PM	00:15
101	Fri	21/10/2022	10:00 PM	00:15
102	Fri	21/10/2022	10:15 PM	00:15
103	Fri	21/10/2022	10:30 PM	00:15
104	Fri	21/10/2022	10:45 PM	00:15
105	Fri	21/10/2022	11:00 PM	00:15
106	Fri	21/10/2022	11:15 PM	00:15
107	Fri	21/10/2022	11:30 PM	00:15
108	Fri	21/10/2022	11:45 PM	00:15
109	Sat	22/10/2022	12:00 AM	00:15
110	Sat	22/10/2022	12:15 AM	00:15
111	Sat	22/10/2022	12:30 AM	00:15
112	Sat	22/10/2022	12:45 AM	00:15
113	Sat	22/10/2022	01:00 AM	00:15
114	Sat	22/10/2022	01:15 AM	00:15
115	Sat	22/10/2022	01:30 AM	00:15
116	Sat	22/10/2022	01:45 AM	00:15
117	Sat	22/10/2022	02:00 AM	00:15
118	Sat	22/10/2022	02:15 AM	00:15
119	Sat	22/10/2022	02:30 AM	00:15
120	Sat	22/10/2022	02:45 AM	00:15
121	Sat	22/10/2022	03:00 AM	00:15
122	Sat	22/10/2022	03:15 AM	00:15
123	Sat	22/10/2022	03:30 AM	00:15
124	Sat	22/10/2022	03:45 AM	00:15
125	Sat	22/10/2022	04:00 AM	00:15
126	Sat	22/10/2022	04:15 AM	00:15
127	Sat	22/10/2022	04:30 AM	00:15
128	Sat	22/10/2022	04:45 AM	00:15
129	Sat	22/10/2022	05:00 AM	00:15
130	Sat	22/10/2022	05:15 AM	00:15
131	Sat	22/10/2022	05:30 AM	00:15
132	Sat	22/10/2022	05:45 AM	00:15
133	Sat	22/10/2022	06:00 AM	00:15
134	Sat	22/10/2022	06:15 AM	00:15
135	Sat	22/10/2022	06:30 AM	00:15
136	Sat	22/10/2022	06:45 AM	00:15
137	Sat	22/10/2022	07:00 AM	00:15
138	Sat	22/10/2022	07:15 AM	00:15
139	Sat	22/10/2022	07:30 AM	00:15
140	Sat	22/10/2022	07:45 AM	00:15
141	Sat	22/10/2022	08:00 AM	00:15
142	Sat	22/10/2022	08:15 AM	00:15
143	Sat	22/10/2022	08:30 AM	00:15
144	Sat	22/10/2022	08:45 AM	00:15
145	Sat	22/10/2022	09:00 AM	00:15	5
146	Sat	22/10/2022	09:15 AM	00:15	5
147	Sat	22/10/2022	09:30 AM	00:15	5
148	Sat	22/10/2022	09:45 AM	00:15	5
149	Sat	22/10/2022	10:00 AM	00:15	5
150	Sat	22/10/2022	10:15 AM	00:15	5
151	Sat	22/10/2022	10:30 AM	00:15	5
152	Sat	22/10/2022	10:45 AM	00:15	5
153	Sat	22/10/2022	11:00 AM	00:15	5
154	Sat	22/10/2022	11:15 AM	00:15	5
155	Sat	22/10/2022	11:30 AM	00:15	5
156	Sat	22/10/2022	11:45 AM	00:15	5
157	Sat	22/10/2022	12:00 PM	00:15	5
158	Sat	22/10/2022	12:15 PM	00:15	5
159	Sat	22/10/2022	12:30 PM	00:15	5
160	Sat	22/10/2022	12:45 PM	00:15	5
161	Sat	22/10/2022	01:00 PM	00:15	5
162	Sat	22/10/2022	01:15 PM	00:15	5
163	Sat	22/10/2022	01:30 PM	00:15	5
164	Sat	22/10/2022	01:45 PM	00:15	5
165	Sat	22/10/2022	02:00 PM	00:15	5
166	Sat	22/10/2022	02:15 PM	00:15	5
167	Sat	22/10/2022	02:30 PM	00:15	5
168	Sat	22/10/2022	02:45 PM	00:15	5
169	Sat	22/10/2022	03:00 PM	00:15	5
170	Sat	22/10/2022	03:15 PM	00:15	5
171	Sat	22/10/2022	03:30 PM	00:15	5
172	Sat	22/10/2022	03:45 PM	00:15	5
173	Sat	22/10/2022	04:00 PM	00:15	5
174	Sat	22/10/2022	04:15 PM	00:15	5
175	Sat	22/10/2022	04:30 PM	00:15	5
176	Sat	22/10/2022	04:45 PM	00:15	5
177	Sat	22/10/2022	05:00 PM	00:15	5
178	Sat	22/10/2022	05:15 PM	00:15	5
179	Sat	22/10/2022	05:30 PM	00:15	5
180	Sat	22/10/2022	05:45 PM	00:15	5
181	Sat	22/10/2022	06:00 PM	00:15
182	Sat	22/10/2022	06:15 PM	00:15
183	Sat	22/10/2022	06:30 PM	00:15
184	Sat	22/10/2022	06:45 PM	00:15
185	Sat	22/10/2022	07:00 PM	00:15
186	Sat	22/10/2022	07:15 PM	00:15
187	Sat	22/10/2022	07:30 PM	00:15
188	Sat	22/10/2022	07:45 PM	00:15
189	Sat	22/10/2022	08:00 PM	00:15
190	Sat	22/10/2022	08:15 PM	00:15
191	Sat	22/10/2022	08:30 PM	00:15
192	Sat	22/10/2022	08:45 PM	00:15
193	Sat	22/10/2022	09:00 PM	00:15
194	Sat	22/10/2022	09:15 PM	00:15
195	Sat	22/10/2022	09:30 PM	00:15
196	Sat	22/10/2022	09:45 PM	00:15
197	Sat	22/10/2022	10:00 PM	00:15
198	Sat	22/10/2022	10:15 PM	00:15
199	Sat	22/10/2022	10:30 PM	00:15
200	Sat	22/10/2022	10:45 PM	00:15
201	Sat	22/10/2022	11:00 PM	00:15
202	Sat	22/10/2022	11:15 PM	00:15
203	Sat	22/10/2022	11:30 PM	00:15
204	Sat	22/10/2022	11:45 PM	00:15
205	Sun	23/10/2022	12:00 AM	00:15
206	Sun	23/10/2022	12:15 AM	00:15
207	Sun	23/10/2022	12:30 AM	00:15
208	Sun	23/10/2022	12:45 AM	00:15
209	Sun	23/10/2022	01:00 AM	00:15
210	Sun	23/10/2022	01:15 AM	00:15
211	Sun	23/10/2022	01:30 AM	00:15
212	Sun	23/10/2022	01:45 AM	00:15
213	Sun	23/10/2022	02:00 AM	00:15
214	Sun	23/10/2022	02:15 AM	00:15
215	Sun	23/10/2022	02:30 AM	00:15
216	Sun	23/10/2022	02:45 AM	00:15
217	Sun	23/10/2022	03:00 AM	00:15
218	Sun	23/10/2022	03:15 AM	00:15
219	Sun	23/10/2022	03:30 AM	00:15
220	Sun	23/10/2022	03:45 AM	00:15
221	Sun	23/10/2022	04:00 AM	00:15
222	Sun	23/10/2022	04:15 AM	00:15
223	Sun	23/10/2022	04:30 AM	00:15
224	Sun	23/10/2022	04:45 AM	00:15
225	Sun	23/10/2022	05:00 AM	00:15
226	Sun	23/10/2022	05:15 AM	00:15
227	Sun	23/10/2022	05:30 AM	00:15
228	Sun	23/10/2022	05:45 AM	00:15
229	Sun	23/10/2022	06:00 AM	00:15
230	Sun	23/10/2022	06:15 AM	00:15
231	Sun	23/10/2022	06:30 AM	00:15
232	Sun	23/10/2022	06:45 AM	00:15
233	Sun	23/10/2022	07:00 AM	00:15
234	Sun	23/10/2022	07:15 AM	00:15
235	Sun	23/10/2022	07:30 AM	00:15
236	Sun	23/10/2022	07:45 AM	00:15
237	Sun	23/10/2022	08:00 AM	00:15	0
238	Sun	23/10/2022	08:15 AM	00:15	0
239	Sun	23/10/2022	08:30 AM	00:15	0
240	Sun	23/10/2022	08:45 AM	00:15	0
241	Sun	23/10/2022	09:00 AM	00:15	0
242	Sun	23/10/2022	09:15 AM	00:15	0
243	Sun	23/10/2022	09:30 AM	00:15	0
244	Sun	23/10/2022	09:45 AM	00:15	0
245	Sun	23/10/2022	10:00 AM	00:15	0
246	Sun	23/10/2022	10:15 AM	00:15	0
247	Sun	23/10/2022	10:30 AM	00:15	0
248	Sun	23/10/2022	10:45 AM	00:15	0
249	Sun	23/10/2022	11:00 AM	00:15	0
250	Sun	23/10/2022	11:15 AM	00:15	0
251	Sun	23/10/2022	11:30 AM	00:15	0
252	Sun	23/10/2022	11:45 AM	00:15	0
253	Sun	23/10/2022	12:00 PM	00:15	0
254	Sun	23/10/2022	12:15 PM	00:15	0
255	Sun	23/10/2022	12:30 PM	00:15	0
256	Sun	23/10/2022	12:45 PM	00:15	0
257	Sun	23/10/2022	01:00 PM	00:15	0
258	Sun	23/10/2022	01:15 PM	00:15	0
259	Sun	23/10/2022	01:30 PM	00:15	0
260	Sun	23/10/2022	01:45 PM	00:15	0
261	Sun	23/10/2022	02:00 PM	00:15	0
262	Sun	23/10/2022	02:15 PM	00:15	0
263	Sun	23/10/2022	02:30 PM	00:15	0
264	Sun	23/10/2022	02:45 PM	00:15	0
265	Sun	23/10/2022	03:00 PM	00:15	0
266	Sun	23/10/2022	03:15 PM	00:15	0
267	Sun	23/10/2022	03:30 PM	00:15	0
268	Sun	23/10/2022	03:45 PM	00:15	0
269	Sun	23/10/2022	04:00 PM	00:15	0
270	Sun	23/10/2022	04:15 PM	00:15	0
271	Sun	23/10/2022	04:30 PM	00:15	0
272	Sun	23/10/2022	04:45 PM	00:15	0
273	Sun	23/10/2022	05:00 PM	00:15	0
274	Sun	23/10/2022	05:15 PM	00:15	0
275	Sun	23/10/2022	05:30 PM	00:15	0
276	Sun	23/10/2022	05:45 PM	00:15	0
277	Sun	23/10/2022	06:00 PM	00:15	0
278	Sun	23/10/2022	06:15 PM	00:15	0
279	Sun	23/10/2022	06:30 PM	00:15	0
280	Sun	23/10/2022	06:45 PM	00:15	0
281	Sun	23/10/2022	07:00 PM	00:15	0
282	Sun	23/10/2022	07:15 PM	00:15	0
283	Sun	23/10/2022	07:30 PM	00:15	0
284	Sun	23/10/2022	07:45 PM	00:15	0
285	Sun	23/10/2022	08:00 PM	00:15
286	Sun	23/10/2022	08:15 PM	00:15
287	Sun	23/10/2022	08:30 PM	00:15
288	Sun	23/10/2022	08:45 PM	00:15
289	Sun	23/10/2022	09:00 PM	00:15
290	Sun	23/10/2022	09:15 PM	00:15
291	Sun	23/10/2022	09:30 PM	00:15
292	Sun	23/10/2022	09:45 PM	00:15
293	Sun	23/10/2022	10:00 PM	00:15
294	Sun	23/10/2022	10:15 PM	00:15
295	Sun	23/10/2022	10:30 PM	00:15
296	Sun	23/10/2022	10:45 PM	00:15
297	Sun	23/10/2022	11:00 PM	00:15
298	Sun	23/10/2022	11:15 PM	00:15
299	Sun	23/10/2022	11:30 PM	00:15
300	Sun	23/10/2022	11:45 PM	00:15
Sheet1

Cell Formulas
Range	Formula
A13:A300	A13	=TEXT(B13,"ddd")
B13	B13	=D10
B14:B108,B110:B204,B206:B300	B14	=B13
B109,B205	B109	=B108+1

 

[bebo021999]

In E13:
If return blank where 0:

=IFERROR(1/1/(C13+10^-10=INDEX(MEDIAN(C13+10^-10,VLOOKUP(A13,$A$2:$D$7,{2,3},0)),))*VLOOKUP(A13,$A$2:$D$7,4,0),"")

If returns 0:

=IFERROR((C13+10^-10=INDEX(MEDIAN(C13+10^-10,VLOOKUP(A13,$A$2:$D$7,{2,3},0)),))*VLOOKUP(A13,$A$2:$D$7,4,0),"")

 

[Marklarbear]

In E13:
If return blank where 0:

=IFERROR(1/1/(C13+10^-10=INDEX(MEDIAN(C13+10^-10,VLOOKUP(A13,$A$2:$D$7,{2,3},0)),))*VLOOKUP(A13,$A$2:$D$7,4,0),"")

If returns 0:

=IFERROR((C13+10^-10=INDEX(MEDIAN(C13+10^-10,VLOOKUP(A13,$A$2:$D$7,{2,3},0)),))*VLOOKUP(A13,$A$2:$D$7,4,0),"")

Cheers bebo - exactly what I was after