Seeking Formula Assistance: Calculating Amount of Time in 2 Different Shift Differential Periods

[Toddeus5]

Greetings,
I've reviewed multiple postings on this site regarding Time Keeping formulas but haven't exactly found a water-proof setup yet.

Situation: KRONOS globally is down.

Background: Ransomware attack.

Assessment: We are manually calculating total hours of employees, and also separating out the amount of hours that fall into 2 different shift differential periods.

Shift Differential 4210: runs from 1500 - 2300
Shift Differential 4250: runs from 2300 - 0700

Example: John works from 1/3/2022, 1830 through 1/4/2022, 0730. Doing the simple math, that is 13 straight hours, and anything after 6 consecutive hours receives an automatic 30 minute lunch break deduction. John's paid hours would be 12.5. Separting this down further, he would spend his 1st 4.5 hours of the shift in differential 4210, and the remaining 8 hours of the shift in differential 4250. How can we reliably calculate these two time values using the reference ranges above?

Recommendations: Seeking yours. : ) Before I embarass you with my current attempts at dissecting this logic, I'd love to hear if others have already successfully conquered this.

Many thanks!

 

[BSALV]

Map2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1				shift name	4250	3rd shift	4210	4250	3rd shift	4210	4250	3rd shift	4210	4250	sum
2				start hours	00:00	07:00	15:00	23:00	31:00	39:00	47:00
3					0:00	7:00	15:00	23:00	7:00	15:00	23:00
4	start	end	starthour	endhour
5	3/01/2022 18:30	4/01/2022 7:30	18:30	31:30	00:00	00:00	04:30	08:00	00:30	00:00	00:00	00:30	04:30	08:00	13:00
6
7
Blad1

Cell Formulas
Range	Formula
I2:K2	I2	=+F2+1
E3:K3	E3	=+E2
C5	C5	=MOD(A5,1)
D5	D5	=MOD(B5,1)+(TRUNC(A5)<>TRUNC(B5))
E5:H5	E5	=MAX(0,MIN(F$2,$D5)-MAX(E$2,$C5))
I5:K5	I5	=MAX(0,MIN(K$2,$D5)-MAX(I$2,$C5))
L5:N5	L5	=SUMPRODUCT(($E$1:$K$1=L$1)*$E5:$K5)
O5	O5	=SUM(L5:N5)

 

[BSALV]

without substracting 0.5 hour break
otherwise a solution with VBA

 

[Toddeus5]

Thank you so much @BSALV, that seems to be working perfectly. I added a quick extra formula to tackle the lunch break decision. This will save a ton of time. Thank you!

 

[habiba]

Why do you have 0:00 in column E2? If you have video tutorial please share.

Thank you

 

[BSALV]

you can start working every hour of a certain day, until 23:59 the next day.
So E2 is 0, for the part of shift 4250 between 0:00-7:00 of the 1st day.