Hours worked to be distributed into day shift, evening shift, and overnight shift

Arnaldo_Longart 

Start by reading the article on the LET function (I listed the URL in the _Info worksheet). You'll learn that it contains user-assigned names paired with cell references/calculations. The last argument-which is not paired-is the calculation that the function returns.

At its core, the last calculation in each LET function subtracts:

• the later (MAX) of the shift start time and the check in time
• from
• the earlier (MIN) of the shift end time and the check out time (the latter of which may have been adjusted up by one day).

The surrounding MAX function uses the larger of zero or the result of that subtraction. (If the times were entered incorrectly, the subtraction might create a negative number, which is not a valid time interval. This function replaces such a negative number with zero.)

The calculation for the overnight shift includes adding the result of an IF function because the employee may have checked in before midnight, and worked into the overnight shift (therefore, again, check in time is a later time than check out time). (Technically, that IF function should have subtracted shift_start from the result of the MIN function, but as shift_start in this case is midnight (=zero), it would be a wasted subtraction anyway.) Peter, Charles, Kate, Gary, Leonard, Paula, Thomas, and William are examples.

Similarly, the calculation for the "ordinary hours" includes adding the result of an IF function because the employee may have checked in before midnight, and worked all the way into the "ordinary hours" shift (therefore, check in time is a later time than check out time). Peter and Charles are examples.

So you can see, storing just times (rather than date-times) complicates the logic needed in the formulas. And if any employee were to work more than 24 hours straight, storing just the times means the formulas cannot detect and handle that situation correctly.