INDEX Function

Pippy79

Board Regular
Joined
Nov 18, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
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
BCD
1Will be 2 days before MP DayWill be 2 days before Collection date
2MP Day (can be any date)Collection DayCollection TimeDispatch Notification
3MonSat13:00Thurs
4TueSun13:00Fri
5WedSun13:00Fri
6ThuWed11:00Mon
7FriWed11:00Mon
8SatWed11:00Mon
9SunSat13:00Thurs

for example;

MP DayDispatch NotificationCollection DayCollection Time
Wed, 02-Feb-22
Fri, 28-Jan-22​
Sun, 30-Jan-22​
13:00​

Currently formula above displays:
MP DayDispatch NotificationCollection DayCollection Time
Wed, 02-Feb-22FriSun13:00

Could it show:
MP DayDispatch NotificationCollection DayCollection Time
Wed, 02-Feb-22Fri, 28-Jan-22Sun, 30-Jan-2213: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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,562
If I understand correctly, try this:

Book1
ABCDEFGHIJ
1Will be 2 days before MP DayWill be 2 days before Collection dateMP DayDispatch NotificationCollection DayCollection Time
2MP Day (can be any date)Collection DayCollection TimeDispatch NotificationWed, 02-Feb-22FriSun13:00
3MonSat13:00Thu
4TueSun13:00FriWed, 02-Feb-22Fri, 28-Jan-22Sun, 30-Jan-2213:00
5WedSun13:00Fri
6ThuWed11:00Mon
7FriWed11:00Mon
8SatWed11:00Mon
9SunSat13:00Thu
Sheet37
Cell Formulas
RangeFormula
H2:J2H2=INDEX(FILTER($B$3:$D$9,$A$3:$A$9=TEXT(G2,"ddd")),{3,1,2})
H4:I4H4=G4-MATCH(VLOOKUP(TEXT(G4,"ddd"),$A$3:$D$9,{4,2},0),TEXT(SEQUENCE(7,,G4,-1),"ddd"),0)+1
J4J4=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.
 
Solution

Pippy79

Board Regular
Joined
Nov 18, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
Thanks Eric, this worked perfectly!!
 

Forum statistics

Threads
1,181,947
Messages
5,932,918
Members
436,869
Latest member
ABGTH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top