Help with IF statement depending on day

[EvoUK]

Hi,

I would really appreciate some assistance with writing a conditional IF statement depending on the day of the week (I tried but realised in the decade since my formula computer classes I barely remember anything other than knowing what is likely possible).

I would like to implement an IF statement into my document which would select either formula A if the day of the week (for example in cell B2) is either the weekend (e.g. Sat or Sun) and if its not, then use formula B. Both formulas are identical but gather data from two different sets of tables (one for weekdays and the either for weekends).

Formula A:

=XLOOKUP($B$1+1/86400,DATA!$L$4:$L$150,DATA!$L$4:$L$150,"not found",1)

Formula B:

=XLOOKUP($E$4+1/86400,DATA!$L$4:$L$150,DATA!$L$4:$L$150,"not found",1)

I have managed to get excel to calculate the current day of the week (prob not the most sophisticated way..) by calculating the current date in one cell

=TODAY()

And calculating the text date of the week by using

=TEXT(D2,"dddd")

.

Any help with an example of how I could use an IF condition with these example formulas would be appreciated.

 

[EvoUK]

That worked perfectly. Thanks again for all your help!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[EvoUK]

Hi again,

I am really sorry to keep being a bother..

I tried inserting my other index formulas into the conditional day selector formula (by replacing the Xlookup segment with Index) however I keep getting a false message instead of the data? Does the formula need to be modified for index?

=IF(WEEKDAY(E2,2)=6,INDEX(data_pad2occ1!$H$4:$H$75,MATCH($B4,data_pad2occ1!$I$4:$I$75,0),IF(WEEKDAY(E2,2)=7,INDEX(data_pad2occ1!$N$4:$N$75,MATCH($B4,data_pad2occ1!$O$4:$O$75,0),INDEX(data_pad2occ1!$B$4:$B$75,MATCH($B4,data_pad2occ1!$C$4:$C$75,0))))))

Weekday
Loc

INDEX(data_pad2occ1!$B$4:$B$75,MATCH($B4,data_pad2occ1!$C$4:$C$75,0))

Saturday

INDEX(data_pad2occ1!$H$4:$H$75,MATCH($B4,data_pad2occ1!$I$4:$I$75,0))

Sunday
Loc

INDEX(data_pad2occ1!$N$4:$N$75,MATCH($B4,data_pad2occ1!$O$4:$O$75,0))

 

[Fluff]

There should be 2 closing brackets after each index function, not one.

 

[EvoUK]

There should be 2 closing brackets after each index function, not one.

Amazing! I am truly in awe of your Excel sorcery skills.
Thank you again!

 

[Fluff]

My pleasure.

 

[EvoUK]

Hi everyone,

Back again. I hope you are all doing well.

The previous formula (above) has been working great.
However, I was wondering if it was possible to add another level of complexity to it?

Recap of the current formula:

=IF(WEEKDAY(E3,2)=6, XLOOKUP($B$1+1/86400,[B]data_pad2occ1![/B]$I$4:$I$76,[B]data_pad2occ1![/B]$I$4:$I$76,"not found",1), IF(WEEKDAY(E3,2)=7, XLOOKUP($B$1+1/86400,data_pad2occ1!$O$4:$O$76,data_pad2occ1!$O$4:$O$76,"not found",1), XLOOKUP($B$1+1/86400,data_pad2occ1!$C$4:$C$76,data_pad2occ1!$C$4:$C$76,"not found",1)))

The dataset is filled with times.
Based on the current day and time, the formula looks up 'time' from the relevant dataset and locates the 'time' from the table which is nearest the current time.
Currently, there are 3 different datasets the formula works from: Weekday, Saturday and Sunday. Depending on the current day, the formula selects the relevant dataset to use (either weekday or Sat or Sun sets).

Current time: B1
Current day: E3

Primary datasets (located on tab data_pad2occ1)
$C$4:$C$76 Weekday
$I$4:$I$76 Saturday
O$4:$O$76 Sunday


The issue I have is: The primary datasets (Weekday, Sat and Sun) as above doesn't change and their current setup is fine.
However, I have supplementary datasets for each day of the week (the data within these sets DO change every week). I am hoping it is possible to modify the formula to also include data from the supplementary datasets depending on the day of the week?

E.g. If today is Monday, the formula will work on the data from the primary weekday dataset (as it currently does):

data_pad2occ1!$C$4:$C$76

BUT ALSO include data from the relevant supplementary set: e.g. tab_B!$A$1:$C$76 (assuming this is the dataset for Monday)

assuming tab_B contains data for the supplementary sets

A1:76 Monday
B1:76 Tuesday
C1:76 Wednesday

etc going up to Sunday.

I am hoping this is the simplest way to solve this issue? Any alternative solutions would be appreciated.

 

[Fluff]

As this is a significantly different question it needs a new thread. Thanks