We have a time worked, given by a "Time In" and a Time Out". Those two times are recorded to a precision of 1 second, and they occur on either the same day or sequential days, so a date stamp and a time stamp comprise the Time In and the Time Out. I am assuming that in no case can someone work more than 24 hours, and that the time worked is continuous, i.e., a single worked time block. We want to determine at least two things: 1) Does any portion of the time worked lie within a time block of 10 PM to 8 AM...akin to a night shift? 2) If so, then did the amount of time worked during this time block equal or exceed 3 hours?
Rather than trying to deal with times on different days, I propose establishing a 2-day window beginning at midnight of Day 1 (i.e., the day associated with Time In) and ending just before midnight of Day 2 (the day after the day associated with Time In). By doing this, we can map the Time In and the Time Out to this window. We can also conveniently map 10 PM and 8 AM to this same window. This facilitates visualizing the problem and constructing formulas to answer the questions. Upon sketching the timeline, we see some issues: the time block of interest spans two days, from midnight of Day 1 through 8 AM of Day 1, from 10 PM of Day 1 through 8 AM of Day 2, and from 10 PM of Day 2 until midnight leading into Day 3, labeled as blocks A, B, and C, respectively. It may be conceivable for someone to work long hours such that they have time in more than one of these time blocks.
Excel defaults to expressing Dates in terms of days and fractional days, so I maintain this convention and treat the window as running from [0,2]. Then the night shift time blocks map as (0,0.333) d, (0.917,1.333) d, and (1.917,2) d. I did not automate this part of the worksheet. The user needs to manually break up any time blocks to fill the entire 2-day window, and then create a table of start and end times for each of the blocks, paying special attention to compute these end points using formulas, e.g., 10 PM on Day 2 is =1+22/24, while 10 PM on Day 1 is =22/24. Similarly, 8 AM on Day 1 is =8/24, and 8 AM on Day 2 is =1+8/24.
The beginning of Day 1 (midnight leading into Day 1) is readily determined using the DATE function applied to the worked Time In, and this establishes time "0". Similary, decimal days relative to time 0 are found via subtraction (actual date&time - beginning of Day 1) for Time In (I) and Time Out (O). Then we examine each of the A,B,C time blocks to determine hours worked during those time blocks. Focusing first on Block B---the central one on the timeline---we have a number of possibilities shown below as 1B-7B. Technically, 6B reverts to 1B because our definition of Day 1 is the day when Time In is recorded. All of these feature Time In's that begin after Block A on Day 1, and the five scenarios address every possibility where a Time Out could occur, with the provision that no Time Out occurs within Block C (this will be addressed later).
The spreadsheet formulas were developed and confirmed against contrived Time In's and Time Out's that satisfied scenarios 1B-7B.
Then the same formula is applied to the other two time blocks to determine the hours worked in each. Now, I am not certain what to do with these results, as the original question is somewhat unclear. Is the question whether 3 or more hours are being worked only during the central time block...block B? Or is the question whether the sum of any hours worked within any of these blocks during a single day >= 3 h. The implication of the latter question being that a Time In of 7 AM and a Time Out of 11 PM gives 2 h total within the night shift time blocks...1 hour in Block A and 1 hour in Block B. The solution presented here gives this breakdown.
One note, this approach will fail under scenarios 9A and 8B because those have Time Outs that extend beyond the upper map limit of 2 days. This approach could be easily extended to include an extra 12 or 24 hours to correctly calculate these situations, but I do not know if it is necessary...or feasible or likely that someone would work for more than 24 hours straight.
I encountered one issue with this approach and wanted to make others aware of it, hoping that someone has ideas for addressing it. If I take one of the examples having a Time In of 9/9/2019 13:11:41 and a Time Out of 9/9/2019 21:36:47, the 4-part nature of the equation to calculate the amount of time worked within a time block appears to be vulnerable to computational precision errors. Rather than return 0, it returns a "days worked" value of -5.55111512312578E-17. Evaluation of the formula confirms that it performs the intended computations, but the expected value is 0. While the computed result is, for all intents and purposes, 0, this causes a problem for any logical of IF statements checking for whether the value is truly 0, as might be the case if one wanted to know whether any time was worked during a block. As a work-around, I would set a tolerance (TOL) of 0.001 s, converted to hours, and then check whether and very near 0 value is smaller than TOL, and if so, set it to 0.
Columns Q:Z were left here to facilitate investigating this issue, if there is any interest. One can see indications of the problem when looking at my summary of total time worked (from the difference...Time Out - Time In). When parsing this value, originally expressed in days, into hh:mm:ss, the seconds (see Column S) reveal this computational issue. The difference between two times recorded to a precision of 1 second should result in a difference whose precision is also 1 second.
