Hi everyone,
I hope you are all doing well.
I have an IF formula (shown below) which has been working well but I wanted to add another level of complexity to it.
The current formula looks up 'time' from a dataset of different timings and calculates/displays the 'time' from the table which is the next event to the current time.
Currently, there are 3 different datasets which 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).
Excel Formula:
Current time: B1
Current day: E3
Primary datasets locations:
(All are located on sheet data_pad2occ1)
$C$4:$C$76 Weekday
$I$4:$I$76 Saturday
O$4:$O$76 Sunday
The issue I have is:
I have supplementary datasets for each day of the week (the data within these sets DO change every week).
The primary datasets (Weekday, Sat and Sun) as above doesn't change and their current setup is fine. I am hoping it is possible to modify the formula to use both the primary dataset (as it currently does) BUT 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 location for the dataset for Monday).
So when the IF formula calculates the next time event, it will be based on both the Weekday dataset and the Supplementary Monday dataset. And the same for if today was Saturday, it would use the primary Saturday dataset + the supplementary Saturday dataset.
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? How do I modify the formula to achieve this function?
Any alternative solutions would also be appreciated.
Thanks in advance
I hope you are all doing well.
I have an IF formula (shown below) which has been working well but I wanted to add another level of complexity to it.
The current formula looks up 'time' from a dataset of different timings and calculates/displays the 'time' from the table which is the next event to the current time.
Currently, there are 3 different datasets which 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).
Excel Formula:
Excel 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)))
Current time: B1
Current day: E3
Primary datasets locations:
(All are located on sheet data_pad2occ1)
$C$4:$C$76 Weekday
$I$4:$I$76 Saturday
O$4:$O$76 Sunday
The issue I have is:
I have supplementary datasets for each day of the week (the data within these sets DO change every week).
The primary datasets (Weekday, Sat and Sun) as above doesn't change and their current setup is fine. I am hoping it is possible to modify the formula to use both the primary dataset (as it currently does) BUT 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 location for the dataset for Monday).
So when the IF formula calculates the next time event, it will be based on both the Weekday dataset and the Supplementary Monday dataset. And the same for if today was Saturday, it would use the primary Saturday dataset + the supplementary Saturday dataset.
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? How do I modify the formula to achieve this function?
Any alternative solutions would also be appreciated.
Thanks in advance