Google Sheets: Determine whether meeting falls within worker hours-CodePudding

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

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

And results:

CodePudding user response：

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: