In a certain Organisation, assume that there are two work schedules – one that runs from Friday to Thursday and another that runs from Sunday to Saturday. In the image below, Work schedule 1 is in range A2:C4 and Work schedule 2 is in range E2:G4. In each dataset, the start date is the first column i.e. the week starts from there. So from 28-Dec-2018 to 29-Dec-2018, only workers 1 and 2 would work but from 30-Dec-2018 to 03-Jan-2019, all 4 workers would work. The objective is to create a combined work schedule as shown in range I2:N7.
The solution is dynamic for new rows that will be added to the source data Tables. I have solved this problem using Power Query a.k.a. Data > Get & Transform in Excel 2016 and higher versions.
You may download my solution workbook from here.