2

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

a screenshot of the spreadsheet

Is it possible to cause the cells at column C to perform as described in here? If so, how?

Improve this question

1 Answer 1

Reset to default
2

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)

enter image description here

ORIGINAL ANSWER

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.

Shifts time slots

Improve this answer
10
  • 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. Commented 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:44 no employee will be in the office at 19:45. Therefore, the info in C3 must be NO. Commented 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. Commented 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. Commented Dec 28, 2019 at 22:49
  • 1
    "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. Commented Dec 29, 2019 at 3:44

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.