3 days in a row, then for 7 consecutive weeks on the day

[tmacbees]

Hi all,

I wonder if someone might be able to help with the following. I think xlookup might be the solution but I can't get my head around it.

The requirement is: If the person you have requested prayers for has passed away, their name is read out during the daily practice for 3 days in a row and then for 7 consecutive weeks on the day of the week of their passing.

Here's a proposeal of how this could look in a way that could then be sortable. I'm using Excel: 365

 

[JamesCanale]

MrExcelPlayground16.xlsx
	A	B	C	D	E
1	Name	Passing	1/1/2023	Date	Pray for
2	John	1/1/2023		1/1/2023
3	Fred	1/14/2023		1/2/2023	John
4	Harry	1/30/2023		1/3/2023	John
5	Mary	2/3/2023		1/4/2023	John
6	Sally	2/10/2023		1/5/2023
7	Joan	2/20/2023		1/6/2023
8	Wilhelm	2/25/2023		1/7/2023
9	Maverick	2/26/2023		1/8/2023	John
10	Goose	2/26/2023		1/9/2023
11				1/10/2023
12				1/11/2023
13				1/12/2023
14				1/13/2023
15				1/14/2023
16				1/15/2023	John, Fred
17				1/16/2023	Fred
18				1/17/2023	Fred
19				1/18/2023
20				1/19/2023
21				1/20/2023
22				1/21/2023	Fred
23				1/22/2023	John
24				1/23/2023
25				1/24/2023
26				1/25/2023
27				1/26/2023
28				1/27/2023
29				1/28/2023	Fred
30				1/29/2023	John
31				1/30/2023
32				1/31/2023	Harry
33				2/1/2023	Harry
34				2/2/2023	Harry
35				2/3/2023
36				2/4/2023	Fred, Mary
37				2/5/2023	John, Mary
38				2/6/2023	Harry, Mary
39				2/7/2023
40				2/8/2023
41				2/9/2023
42				2/10/2023	Mary
43				2/11/2023	Fred, Sally
44				2/12/2023	John, Sally
45				2/13/2023	Harry, Sally
46				2/14/2023
47				2/15/2023
48				2/16/2023
49				2/17/2023	Mary, Sally
50				2/18/2023	Fred
51				2/19/2023	John
52				2/20/2023	Harry
53				2/21/2023	Joan
54				2/22/2023	Joan
55				2/23/2023	Joan
56				2/24/2023	Mary, Sally
57				2/25/2023	Fred
58				2/26/2023	Wilhelm
59				2/27/2023	Harry, Joan, Wilhelm, Maverick, Goose
60				2/28/2023	Wilhelm, Maverick, Goose
61				3/1/2023	Maverick, Goose
62				3/2/2023
63				3/3/2023	Mary, Sally
64				3/4/2023	Fred, Wilhelm
65				3/5/2023	Maverick, Goose
66				3/6/2023	Harry, Joan
67				3/7/2023
68				3/8/2023
69				3/9/2023
70				3/10/2023	Mary, Sally
71				3/11/2023	Wilhelm
72				3/12/2023	Maverick, Goose
73				3/13/2023	Harry, Joan
74				3/14/2023
75				3/15/2023
76				3/16/2023
77				3/17/2023	Mary, Sally
78				3/18/2023	Wilhelm
79				3/19/2023	Maverick, Goose
80				3/20/2023	Harry, Joan
81				3/21/2023
82				3/22/2023
83				3/23/2023
84				3/24/2023	Mary, Sally
85				3/25/2023	Wilhelm
86				3/26/2023	Maverick, Goose
87				3/27/2023	Joan
88				3/28/2023
89				3/29/2023
90				3/30/2023
91				3/31/2023	Sally
92				4/1/2023	Wilhelm
93				4/2/2023	Maverick, Goose
94				4/3/2023	Joan
95				4/4/2023
96				4/5/2023
97				4/6/2023
98				4/7/2023
99				4/8/2023	Wilhelm
100				4/9/2023	Maverick, Goose
Sheet4

Cell Formulas
Range	Formula
D2:D101	D2	=SEQUENCE(100,1,C1)
E2:E100	E2	=TEXTJOIN(", ",TRUE,(IF(SIGN((($D2>$B$2:$B$10)*($D2<=$B$2:$B$10+3))+((WEEKDAY($D2)=WEEKDAY($B$2:$B$10))*($D2<$B$2:$B$10+50)*($D2>$B$2:$B$10))),$A$2:$A$10,"")))
Dynamic array formulas.

 

[Anthony47]

Another option, in addition to the solution suggested by James, above...

Create in columns A:B the list with the names and DateOfPassing
Insert in F1 the Date, maybe using the formula =TODAY()
Get the "List of the Date" using in G2 the following Office 365 formula:

=LET(Names,A2:B2000,DayDt,F1,dDate,INDEX(Names,0,2),wDay,WEEKDAY(dDate,2),dayList,FILTER(Names,((DayDt-dDate)<4)+((DayDt-dDate)<49)*(wDay=WEEKDAY(DayDt,2))),SORT(dayList,2,-1))

As shown in the attached image and the following XL2BB minisheet
(the week of the day in column C and in F2 are only for visual checking)
Not sure if in ((DayDt-dDate)<4) the "4" has to be 4 or 3; 4 returns today dates including Feb 25, whereas 3 would return dates from Feb 26

Cartel1
	A	B	C	D	E	F	G	H	I
1	Name	Date of Passing	Wday		Date:	28/02/2023	List of the Day
2	NVE3	28-feb-23	2			2	NVE3	28-feb-23
3	CGU25	27-feb-23	1				CGU25	27-feb-23
4	GSJ25	26-feb-23	7				GSJ25	26-feb-23
5	YLQ14	25-feb-23	6				YLQ14	25-feb-23
6	WOO18	25-feb-23	6				WOO18	25-feb-23
7	YDN21	25-feb-23	6				YDN21	25-feb-23
8	TXA10	24-feb-23	5				BUQ19	21-feb-23
9	URN11	23-feb-23	4				JCA24	14-feb-23
10	KVJ22	22-feb-23	3
11	BUQ19	21-feb-23	2
12	EPT5	19-feb-23	7
13	IMJ15	19-feb-23	7
14	OQO2	18-feb-23	6
15	YTH4	18-feb-23	6
16	RFY6	18-feb-23	6
17	TIC8	18-feb-23	6
18	VCH12	18-feb-23	6
19	WGI13	17-feb-23	5
20	VTF16	17-feb-23	5
21	JCA24	14-feb-23	2
22	JKP7	12-feb-23	7
Foglio1

Cell Formulas
Range	Formula
F2	F2	=WEEKDAY(F1,2)
G2:H9	G2	=LET(Names,A2:B200,DayDt,F1,dDate,INDEX(Names,0,2),wDay,WEEKDAY(dDate,2),dayList,FILTER(Names,((DayDt-dDate)<4)+((DayDt-dDate)<49)*(wDay=WEEKDAY(DayDt,2))),SORT(dayList,2,-1))
C2:C22	C2	=WEEKDAY(B2,2)
Dynamic array formulas.

 

[tmacbees]

wao, many thanks for the quick replies. fiddling about with xlookup and also with some help from a colleague we came up with

c12 =	=XLOOKUP(B10,$G$2:$G$4,$I$2:$I$4," ")
e12 =	=B10+((ROUNDUP((TODAY()-B10)/7,0))*7)
i12 =	"=XLOOKUP(B12,$G$9:$G$17,$I$9:$I$17,,-1)"

 

[tmacbees]

sorry, wrong image