# How to concatenate several "filled" time slots in a spreadsheet and then check if they completely fill a "main" time slot?

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 `W`).

The timespan determined by columns `G` and `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?

EDIT Looking back at your sheet I just noticed:
"If no employee is assigned to any shift, keep the cell blank"

The updated formula (taking into account all blank cells) would be

``````=ArrayFormula(IF((
(H3:H33="")*(I3:I33="")*(J3:J33="")*(K3:K33="")*(L3:L33="")*(M3:M33="")*(N3:N33="")
*(O3:O33="")*(P3:P33="")*(Q3:Q33="")*(R3:R33="")*(S3:S33="")*(T3:T33="")*(U3:U33="")*(V3:V33="")),"",
IF(
((TEXT(G3:G33,"HH:MM")="08:15") * (
+(H3:H33<>0)+(I3:I33<>0)+(J3:J33<>0)) * (
+(+(K3:K33<>0)+(L3:L33<>0)+(M3:M33<>0))+(+(N3:N33<>0)+(O3:O33<>0)+(P3:P33<>0))) * (
+(+(Q3:Q33<>0)+(R3:R33<>0)+(S3:S33<>0))+(+(T3:T33<>0)+(U3:U33<>0)+(V3:V33<>0))) * (
+(+(T3:T33<>0)+(U3:U33<>0)+(V3:V33<>0)))) + (
(TEXT(G3:G33,"HH:MM")="10:00") * (
+(N3:N33<>0)+(O3:O33<>0)+(P3:P33<>0)) * (
+(Q3:Q33<>0)+(R3:R33<>0)+(S3:S33<>0)))

,"YES","NO")))
``````

(I also added one more Conditional formatting rule)

The formula you need to use in cell `C3` is:

``````=ArrayFormula(IF(
((TEXT(G3:G33,"HH:MM")="08:15") * (
+(H3:H33<>0)+(I3:I33<>0)+(J3:J33<>0)) * (
+(+(K3:K33<>0)+(L3:L33<>0)+(M3:M33<>0))+(+(N3:N33<>0)+(O3:O33<>0)+(P3:P33<>0))) * (
+(+(Q3:Q33<>0)+(R3:R33<>0)+(S3:S33<>0))+(+(T3:T33<>0)+(U3:U33<>0)+(V3:V33<>0)))) + (
(TEXT(G3:G33,"HH:MM")="10:00") * (
+(N3:N33<>0)+(O3:O33<>0)+(P3:P33<>0)) * (
+(Q3:Q33<>0)+(R3:R33<>0)+(S3:S33<>0)))
,"YES","NO"))``````

NOTE
We can use neither `AND` nor `OR` in an `ARRAYFORMULA`.
Instead we use `*` and `+` respectively.

• Please check the updated answer and if it solves your question accept it and even upvote it so others can benefit as well. If not, let us know. Dec 28, 2019 at 13:27
• Many thanks for taking the time to create this formula. Unfortunately, it doesn’t solve my question. E.g. row `3` of my dummy spreadsheet tells us (at `G3` and `W3`) that the office will be open 08:15-19:45. Hence, at least 1 employee must be in the office during 08:15-19:45 (i.e. the office can't be empty / left unattended for a single minute in 08:15-19:45). However, based on cells `H3`, `O3` and `Q3`, even though at least 1 employee will be in the office 08:15-19:4`4` no employee will be in the office at 19:4`5`. Therefore, the info in `C3` must be NO. Dec 28, 2019 at 22:00
• For instance, at row `3` your formula checks if cells in `H3:V3` are either blank or filled and then returns `""` if they're all blank, otherwise it returns `YES` if shifts 1 and 2 (or 3) and 4 (or 5) have at least 1 allocated employee when `G3` is 08:15, `YES` if shifts 3 and 4 have at least 1 allocated employee when `G3` is 10:00, and `NO` otherwise. But if e.g. on day 1 (`G3` = 08:15) I change shift 1 to e.g. 08:30-14:00 and allocate employees on shifts 1, 2 and 4 your formula will return `YES` on `C3` even though the office will be empty during 08:15-08:29 and during 19:45. Dec 28, 2019 at 22:23
• The second formula returned an error. I fixed it by changing the first 3 lines of its code to `=ArrayFormula(IF(((H3:H33="")*(I3:I33="")*(J3:J33="")*(K3:K33="")*(L3:L33="")*(M3:M33="")*(N3:N33="")*(O3:O33="")*(P3:P33="")*(Q3:Q33="")*(R3:R33="")*(S3:S33="")*(T3:T33="")*(U3:U33="")*(V3:V33="")),"",` but, anyway, as mentioned before, such formula doesn't detect when the (open) office is empty for 1 or more minutes, and if I change start/end of any shift then such formula is "unaware" of such change. I’ve been trying to figure this problem out for almost a month already, but so far nothing worked. Dec 28, 2019 at 22:49
• "The second formula returned an error.". No it couldn't. An HTML bug prevented it from being recorded correctly on this page (I forgot about this bug-sorry). You can compare it with the one on the already attached image and see it is the correct formula. Dec 29, 2019 at 3:44