Formula help requested

[cjlittlet]

I need help coming up with formulas pretty please.

Backstory: I have to determine the number of weeks (already got help with that) that fall in a specific period and the start and stop dates. We have 4 programs the person could get coverage under, based on when they filed I need to know how many weeks in each program (with start-stop dates) they qualify for. The only thing I could think of was x's next to the date if it's in that range then create a formal that finds the first and last x and displays the start-stop dates.

Open to advise on ways to do this better.

For Example:
Client was covered from 12.26.20 - 6.26.21
Program dates weeks covered
P1: 4.4.20 - 7.25.20 none
P2: 8.1.20 - 9.5.20 none
P3: 1.2.21 - 3.13.21 1.2.21 - 3.13.21
P4: 3.20.21 - 6.26.21 3.20.21 - 6.26.21

dates	12.26.20	06.26.21
Column1	Column2
04.04.20		from	to
04.11.20
04.18.20
04.25.20
05.02.20
05.09.20
05.16.20
05.23.20
05.30.20
06.06.20
06.13.20
06.20.20
06.27.20
07.04.20
07.11.20
07.18.20
07.25.20
Column1	Column2
08.01.20		from	to
08.08.20
08.15.20
08.22.20
08.29.20
09.05.20
09.12.20
Column1	Column2
01.02.21	x	from	to
01.09.21	x
01.16.21	x
01.23.21	x
01.30.21	x
02.06.21	x
02.13.21	x
02.20.21	x
02.27.21	x
03.06.21	x
03.13.21	x
Column1	Column2
03.20.21	x	from	to
03.27.21	x
04.03.21	x
04.10.21	x
04.17.21	x
04.24.21	x
05.01.21	x
05.08.21	x
05.15.21	x
05.22.21	x
05.29.21	x
06.05.21	x
06.12.21	x
06.19.21	x
06.26.21	x

 

[Eric W]

I'm not quite sure what you're looking for, but maybe:

Book1
	A	B	C	D
1
2	dates	12/26/2020	6/26/2021
3
4	Column1	Column2
5	4/4/2020		from	to
6	4/11/2020
7	4/18/2020
8	4/25/2020
9	5/2/2020
10	5/9/2020
11	5/16/2020
12	5/23/2020
13	5/30/2020
14	6/6/2020
15	6/13/2020
16	6/20/2020
17	6/27/2020
18	7/4/2020
19	7/11/2020
20	7/18/2020
21	7/25/2020
22	Column1	Column2
23	8/1/2020		from	to
24	8/8/2020
25	8/15/2020
26	8/22/2020
27	8/29/2020
28	9/5/2020
29	9/12/2020
30	Column1	Column2	1/2/2021	3/13/2021
31	1/2/2021	x	from	to
32	1/9/2021	x
33	1/16/2021	x
34	1/23/2021	x
35	1/30/2021	x
36	2/6/2021	x
37	2/13/2021	x
38	2/20/2021	x
39	2/27/2021	x
40	3/6/2021	x
41	3/13/2021	x
42	Column1	Column2	3/20/2021	6/26/2021
43	3/20/2021	x	from	to
44	3/27/2021	x
45	4/3/2021	x
46	4/10/2021	x
47	4/17/2021	x
48	4/24/2021	x
49	5/1/2021	x
50	5/8/2021	x
51	5/15/2021	x
52	5/22/2021	x
53	5/29/2021	x
54	6/5/2021	x
55	6/12/2021	x
56	6/19/2021	x
57	6/26/2021	x
Sheet17

Cell Formulas
Range	Formula
C4:D4	C4	=IFERROR(AGGREGATE({15,14},6,IF((A5:A21>=B2)*(A5:A21<=C2),A5:A21),1),"")
C22:D22	C22	=IFERROR(AGGREGATE({15,14},6,IF((A23:A29>=B2)*(A23:A29<=C2),A23:A29),1),"")
C30:D30	C30	=IFERROR(AGGREGATE({15,14},6,IF((A31:A41>=B2)*(A31:A41<=C2),A31:A41),1),"")
C42:D42	C42	=IFERROR(AGGREGATE({15,14},6,IF((A43:A57>=B2)*(A43:A57<=C2),A43:A57),1),"")
Dynamic array formulas.

 

[cjlittlet]

YOU ARE A FREAKING LIFE SAVER!!! Thank you so much!!!!