How many hours within worked hours

[jackied]

Hello Everyone,

I need help!

Scenario:

I have a 24-hour labor contract that runs approximately 3,500 hours a month.
The invoice gives me everyone's Time In and Time Out with Total Hours worked.

The issue I am facing is that within the hours worked, we are performing an above-standard service to another group which is billable to them.
We have set hours that we provide this 3rd party service.
The above-standard, billable hours are Monday-Friday from 6pm - 8am.
The invoice I receive gives me the backup and is displayed like this:

12/6/22 Joe Smith, Time In: 0:00, Time Out: 6:00, Hours: 6
12/6/22 Jane Smith, Time In: 6:00, Time Out: 14:00, Hours: 8
12/6/22 John Smith, Time In: 6:00, Time Out: 16:00, Hours: 10
12/6/22 Jake Smith, Time In: 14:00, Time Out: 22:00, Hours: 8
12/6/22 Jack Smith, Time In: 16:00, Time Out: 22:00, Hours: 6
12/6/22 Jackie Smith, Time In: 22:00, Time Out: 0:00, Hours: 2

I need a formula that can tell me how many of the hours worked fall within 6pm and 8am.

Please help! and Thank you!!

 

[iggydarsa]

Depending where your actual data is you might want to change the address of the cells in the formula

=IF(HOUR(C2)>18,24-HOUR(C2), 0)+IF(HOUR(D2)<=8, HOUR(D2), IF(HOUR(D2)>18, HOUR(D2)-18))

 

[jackied]

You are amazing! Thank you so much for this! I appreciate this tremendously.