Calculate Hours Falling Within Defined Windows of Time

I'm working on creating a scheduling tool for Front Desk employees at a hotel. I have rows for up to 20 different employees (Rows 13-32) with employee names in Column B, Shift Start Times for Tuesday (first day of their work week) formatted as Time (i.e., 7:00 AM) in Column C, Shift End Times for Tuesday formatted as Time (i.e., 3:00 PM) in Column D, and a formula calculating each employees number of hours for that day in Column E.

The hotel has three different "Shifts": the "AM Shift" starts at 7:00 AM and runs until 3:00 PM, the "PM Shift" starts at 3:00 PM and runs until 11:00 PM, and the "ON Shift" (overnight) starts at 11:00 PM and runs until 7:00 AM the following day. Employees scheduled hours may mirror the predefined shifts (i.e., an employee may be scheduled to work the AM Shift from 7AM-3PM), or they may be scheduled for hours that fall within two or more predefined shifts (i.e., an employee may be scheduled to work from 12PM-8PM which would be 3 hours in the AM Shift and 5 hours in the PM Shift). For each employee, I need to calculate the number of hours they're scheduled for in the AM Shift, PM Shift, and ON Shift windows so I can then sum them for all employees and arrive at the total AM Shift hours, PM Shift hours, and ON Shift hours scheduled for each day. I've created helper columns to the right and I've been able to figure this out for the AM Shift and PM Shift hours in the attached file, however calculating the ON Shift hours for each employee is proving to be extremely troublesome due to the fact that this shift spans two days. The formula needs to be able to handle blank cells (return 0 for these) and should correctly handle all scenarios including (but not limited to) the following:

• Shift Start Time of 7:00 AM and Shift End Time of 3:00 PM should return 0.
• Shift Start Time of 11:00 PM and Shift End Time of 7:00 AM should return 8.
• Shift Start Time of 10:00 PM and Shift End Time of 9:00 AM should return 8.
• Shift Start Time of 12:00 AM and Shift End Time of 7:00 AM should return 7.
• Shift Start Time of 5:00 PM and Shift End Time of 1:00 AM should return 2.

If someone could please provide a formula I can use in cells AD13:AD32 of the attached workbook, I would be eternally grateful as I've been pulling my hair out for two full days now trying to come up with a solution! Thanks in advance for any assistance you can provide - Robert