Calculate hours within 2 date/time

[Pantoffsky]

Hi,
I've been struggling with the following:

I have 2 lines with 2 timeslots, let's say:

A2= 2022-01-03 21:30
B2= 2022-01-04 07:30

A3= 2022-01-04 21:30
B3= 2022-01-04 04:00

To calculate a rate, I need to know how many hours have been worked between 21:00 and 05:00, because they have a different rate.

How do i calculate these "expensive" hours?

 

[Dave Patton]

It is much better if you post a concise example with the forum's tool XL2BB.
The example should show the expected result.

try the following

Date and Time 2021.xlsm
	A	B	C	D	E	F	G
1			Total	OT	Reg	21:00	05:00
2	2022-01-03 21:30	2022-01-04 07:30	10:00:00	07:30:00	02:30:00
3	2022-01-03 21:30	2022-01-04 04:00	06:30:00	06:30:00	00:00:00
12b

Cell Formulas
Range	Formula
C2:C3	C2	=B2-A2
D2:D3	D2	=1-MAX(F1,MOD(A2,1))+MIN(G1,MOD(B2,1))
E2:E3	E2	=C2-D2