Google Sheets: Determine whether meeting falls within worker hours

Question

I'm trying to determine how many meetings fall outside of a worker's standard business hours. All meeting times come out of the system in my time zone (Pacific Time), however there are workers across time zones. Therefore, I converted every worker's standard business hours according to their time zones, into Pacific Time and am trying to compare the meeting time in PT to their business hours in PT.

I'm having difficulty with the calculation in any situation where a worker's standard business hours are overnight in PT, such as 11 PM - 9 AM and a meeting was held between 4 - 5 AM, for example. I'm currently using the formula:

=IF(A2:A="","",IF(AND(TIMEVALUE(C2)<=TIMEVALUE(A2),TIMEVALUE(D2)>=TIMEVALUE(B2)),"Y","N"))

Where

• Col A = Meeting Start Time
• Col B = Meeting End Time
• Col C = Worker Start Time
• Col D = Worker End Time

I want to determine whether the entirety of the meeting was within the worker's business hours. If it was, the output should be "Y" otherwise the output is "N."

Here is a sample sheet - I have the formula I tried in E2 and I highlighted all of the incorrectly calculated outputs.

Thanks in advance!

Answer 1

Having only the time as the input, google sheets will be confused. Let's use your data for example (See highlighted row 13). The worker start time is 11PM-9AM, however for the meeting which is 4AM - 5AM, it has no way of knowing if the meeting is for today or the next day. Which is why in this formula that I used, I added Today() just to set a dummy date together with the time.

=IF(A2="","",IF(AND(TODAY()+C2<=IF(TIMEVALUE(A2)<TIMEVALUE(C2),TODAY()+A2+1,TODAY()+A2),IF(TIMEVALUE(C2)>TIMEVALUE(D2),TODAY()+1+D2,TODAY()+D2)>=IF(TIMEVALUE(A2)<TIMEVALUE(C2),TODAY()+B2+1,TODAY()+B2)),"Y","N"))

Result:

Answer 2

The function you are looking for is AND(). The rest is correct. I had to look this up as I have never used a google sheet. Other applications use different syntax like && or & or +. You just need to figure out what the application needs.

Basically, an AND() in this case means that every expression needs to be true to actually result in true.

=if(AND(timevalue(A2)>=timevalue(C2),timevalue(B2)<=timevalue(D2)),"Y","N")

You actually have that. Get the first IF out of there and drag and drop the corner of the first row to populate the whole chart.

Double Click the corner in this image to populate all rows:

And results: