INDEX Function

[Pippy79]

Hi Again,

Is there a way to display an actual date instead of the day?

Current formula;

INDEX(FILTER($B$3:$D$9,$A$3:$A$9=TEXT(G2,"ddd")),{3,1,2})

A	B	C	D
1		Will be 2 days before MP Day		Will be 2 days before Collection date
2	MP Day (can be any date)	Collection Day	Collection Time	Dispatch Notification
3	Mon	Sat	13:00	Thurs
4	Tue	Sun	13:00	Fri
5	Wed	Sun	13:00	Fri
6	Thu	Wed	11:00	Mon
7	Fri	Wed	11:00	Mon
8	Sat	Wed	11:00	Mon
9	Sun	Sat	13:00	Thurs

for example;

MP Day	Dispatch Notification	Collection Day	Collection Time
Wed, 02-Feb-22	Fri, 28-Jan-22​	Sun, 30-Jan-22​	13:00​

Currently formula above displays:

MP Day	Dispatch Notification	Collection Day	Collection Time
Wed, 02-Feb-22	Fri	Sun	13:00

Could it show:

MP Day	Dispatch Notification	Collection Day	Collection Time
Wed, 02-Feb-22	Fri, 28-Jan-22	Sun, 30-Jan-22	13:00

the MP dates will change throughout the year.

Collection times are fixed for Wednesday - 11:00 and Saturday and Sunday will always be 13:00.

thanks

 

[Eric W]

If I understand correctly, try this:

Book1
	A	B	C	D	E	F	G	H	I	J
1		Will be 2 days before MP Day		Will be 2 days before Collection date			MP Day	Dispatch Notification	Collection Day	Collection Time
2	MP Day (can be any date)	Collection Day	Collection Time	Dispatch Notification			Wed, 02-Feb-22	Fri	Sun	13:00
3	Mon	Sat	13:00	Thu
4	Tue	Sun	13:00	Fri			Wed, 02-Feb-22	Fri, 28-Jan-22	Sun, 30-Jan-22	13:00
5	Wed	Sun	13:00	Fri
6	Thu	Wed	11:00	Mon
7	Fri	Wed	11:00	Mon
8	Sat	Wed	11:00	Mon
9	Sun	Sat	13:00	Thu
Sheet37

Cell Formulas
Range	Formula
H2:J2	H2	=INDEX(FILTER($B$3:$D$9,$A$3:$A$9=TEXT(G2,"ddd")),{3,1,2})
H4:I4	H4	=G4-MATCH(VLOOKUP(TEXT(G4,"ddd"),$A$3:$D$9,{4,2},0),TEXT(SEQUENCE(7,,G4,-1),"ddd"),0)+1
J4	J4	=VLOOKUP(TEXT(G4,"ddd"),$A$3:$D$9,3,0)
Dynamic array formulas.

Your formula is in H2, mine are in H4 and J4. Also note that I changed Thurs to Thu in column D.

 

[Pippy79]

Thanks Eric, this worked perfectly!!