excel - List values on a schedule based on time and date

Question

I have a collection of raw data stored in a table using headings of

1. Name, String             //Name of employee
2. Job, String              //The job site they are on
3. Start Date (SD), Date    //Starting date
4. End Date (ED), Date      //Ending date
5. Start Time(ST), Time     //Start time each day
6. End Time (ET), Time      //End time each day
7. Saturday, Bool           //Whether they will be onsite on Saturday
8. Sunday, Bool             //Same as above

In an excel sheet, I have a table formatted as such

Raw data

Working Schedule Spreadsheet

In each cell, I currently have this array entered formula

=IFNA(INDEX(INDIRECT(Cells!$B$2), MATCH(1, ($A3 = INDIRECT(Cells!$A$2)) * (C$1 >= INDIRECT(Cells!$C$2)) * (C$1 < INDIRECT(Cells!$D$2)) * ($B3 >= INDIRECT(Cells!$E$2)) * ($B3 < INDIRECT(Cells!$F$2)), 0)), "")
//The INDIRECT references the input table

This currently works, but doesn't check if the employee is scheduled on for Sat or Sun (which default to false). The way it works is by doing a multivariable index-match on all of the variables listed. I cannot figure out how I might be able to also include the boolean bit.

The issue I am having is that if I add (TEXT(C$2, "ddd") = INDIRECT(Cells!$G$2)) into the match, then it will only work if it is on a Saturday, so having a similar statement to check for Sundays won't show at all.

Broken Schedule Spreadsheet using second formula

I hope that makes sense, I can supply more information if needed. Thanks

标签： excel

Answer 1

添加几个条件。

=IFNA(INDEX(INDIRECT(Cells!$B$2),MATCH(1,($A3=INDIRECT(Cells!$A$2))*(C$1>=INDIRECT(Cells!$C$2))*(C$1<INDIRECT(Cells!$D$2))*($B3>=INDIRECT(Cells!$E$2))*($B3<INDIRECT(Cells!$F$2))*IF(WEEKDAY(F$1)=7,INDIRECT(Cells!$G$2),1)*IF(WEEKDAY(F$1)=1,INDIRECT(Cells!$H$2),1),0)),"")