For each row that represents a day of the month (month day 1, month day 2, month day 3 and so on...), I need this spreadsheet to answer
NO at column
C if no employee will be in the office for at least 1 minute between the office's opening hour (column
G) and the office's closing hour (column
The timespan determined by columns
W represents the "main" time slot (the office's working period), and the timespans determined by each work shift (shift 1, shift 2, shift 3, shift 4 and shift 5) represent the time slots that must be concatenated/combined so the resulting (concatenated/combined) timespan/time slot can be compared with the "main" time slot.
Each employee is represented by a code (a single letter): A (Andrew), B (Brandon), C (Christina), D (Daniel), E (Emma), F (Francis), G (George) and H (Harry).
If on day D at least 1 employee code is assigned to a shift (e.g. shift 1), then the spreadsheet needs to understand that on day D there will be at least one person in the office during the time interval (timespan/time slot) represented by such shift.
Therefore, if e.g. employee A is assigned to shift 1 (08:00 till 14:00) on day D, then the spreadsheet needs to understand that on day D there will be at least one employee in the office between 08:00 and 14:00 (such period of time will be occupied or filled). However, if during day D the entire business hours encompasses e.g. 08:15 till 19:45 (this is the "main" time slot), then it's still going to be necessary to assign additional employees to be at the office between 14:01 and 19:45 (because shift 1 provides employee[s] only during 08:15-14:00). The spreadsheet will let you know about such need (of other employee[s] allocated in other work shift[s]) by reporting NO at the column
C (at the row of day D).
Is it possible to cause the cells at column
C to perform as described in here? If so, how?