# 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. – marikamitsos Dec 28 '19 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. – Yuri Sucupira Dec 28 '19 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. – Yuri Sucupira Dec 28 '19 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. – Yuri Sucupira Dec 28 '19 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. – marikamitsos Dec 29 '19 at 3:44