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.

 

[etaf]

=IF(WEEKDAY(B2,2)>5,formula A, formulaB)

 

[Fluff]

How about

=XLOOKUP(IF(WEEKDAY(TODAY(),2)>5,$B$1,$E$4)+1/86400,Data!$L$4:$L$150,Data!$L$4:$L$150,"not found",1)

 

[EvoUK]

=IF(WEEKDAY(B2,2)>5,formula A, formulaB)

That worked great, thank you!

Another question please..
I was thinking of an alternative method for the same aims as above in which I filter the RAW table data to only show entries which are relevant to the day of the week (each entry has a corresponding column that indicates the day of the week that entry applies to).
Then based on this filtered table (which I am hoping to update automatically based on the current day), I use my previous Xlookup formula.

Would it be possible to use conditional formatting to change and select a particular text filter for the pivot table based the day of the week (which is calculated in cell G1 for example)?
E.g. if today was Sunday, then the filter on the pivot table would update accordingly to Sunday or multiple filter entries if required?

 

[Fluff]

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

 

[EvoUK]

=IF(WEEKDAY(B2,2)>5,formula A, formulaB)

Would it be possible to modify this formula to add additional conditions depending on the day of the week?
e.g. if its a weekday use formula A, if its Saturday use formula B, if its Sunday use formula C etc?

 

[Fluff]

How about

=IF(WEEKDAY(B2,2)<6,formula A, IF(WEEKDAY(B2,2)=6,formulaB,formulaC))

 

[etaf]

yes , nested IF
=IF(WEEKDAY(B2,2)>5,formula A, formulaB)

=IF(WEEKDAY(B2,2)=6, formulaB for sat, if( (WEEKDAY(B2,2)=7, formulaC for Sunday , formulaA for weekday)))

 

[EvoUK]

yes , nested IF
=IF(WEEKDAY(B2,2)>5,formula A, formulaB)

=IF(WEEKDAY(B2,2)=6, formulaB for sat, if( (WEEKDAY(B2,2)=7, formulaC for Sunday , formulaA for weekday)))

Hi,

thanks for the quick reply.

I tried the updated formula but keep getting an error. Any ideas where I am going wrong?
The individual formulas for either weekday, Saturday or Sunday work independently.

=IF(WEEKDAY(E2,2)=6, XLOOKUP($B$1+1/86400,data_pad2occ1!$I$4:$I$76,data_pad2occ1!$I$4:$I$76,"not found",1), if((WEEKDAY(E2,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))))

Date: E2

=TODAY()

Weekday

XLOOKUP($B$1+1/86400,data_pad2occ1!$C$4:$C$76,data_pad2occ1!$C$4:$C$76,"not found",1)

Saturday

XLOOKUP($B$1+1/86400,data_pad2occ1!$I$4:$I$76,data_pad2occ1!$I$4:$I$76,"not found",1)

Sunday

XLOOKUP($B$1+1/86400,data_pad2occ1!$O$4:$O$76,data_pad2occ1!$O$4:$O$76,"not found",1)

 

[Fluff]

You have an extra set of brackets, try

=IF(WEEKDAY(E2,2)=6, XLOOKUP($B$1+1/86400,data_pad2occ1!$I$4:$I$76,data_pad2occ1!$I$4:$I$76,"not found",1), IF(WEEKDAY(E2,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)))