How to split hours worked into 3 shift patterns?

[JoeGraf10]

Hello,
I have a time sheet scenario with 3 x shifts that are charged at different premiums. I have classified them as following:
Morning = midnight until 8am - 0:00 to 8:00
Day = 8am til 8pm - 8:00 to 20:00
Night = 8pm til midnight - 20:00 to 24:00

I have created a small table to the side to distinguish the time splits in the day. Eg, 08:00/20:00/24:00 to allot me to =if(start time<08:00,8:00-start time)*24,0) with the start time being 06:00 this works and returns 2 hours.

Not I need to portion out the day shift part of the hours worked say the end time is 21:00, it should then show 12 hours worked between 08:00 and 20:00, and then 1 hour from 20:00 to 21:00.

I have tried a couple "IFS" to try to portion this out and in my head the formula is perfect (it's not) haha

How would you superior excel users work this one out?

Thank you for your help

 

[BSALV]

Map1
	A	B	C	D	E	F
1	0:00
2	8:00
3	20:00
4	24:00:00
5
6
7
8	start	end	shift1	shift2	shift3	total
9	6:30	5:30	7:00	12:00	4:00	23:00
Blad1

Cell Formulas
Range	Formula
C9	C9	=IF(COUNT($A9:$B9)=2,MAX(0,MIN($A$2,$B9+($B9<$A9))-MAX($A9,$A$1))+MAX(0,MIN($A$2+1,$B9+($B9<$A9))-1),"")
D9	D9	=IF(COUNT($A9:$B9)=2,MAX(0,MIN($A$3,$B9+($B9<$A9))-MAX($A9,$A$2)),"")
E9	E9	=IF(COUNT($A9:$B9)=2,MAX(0,MIN($A$4,$B9+($B9<$A9))-MAX($A9,$A$3)),"")
F9	F9	=IF(COUNT($A9:$B9)=2,$B9-$A9+($B9<$A9),"")

 

[JoeGraf10]

Amazing, I will give it a go shortly

Appreciate your time

 

[JoeGraf10]

Worked perfectly, thank you for the help!

Add on if you have the time....

The columns filled in by the employee is a column for date and also the start and finish time. Special rates apply to Sunday and also Bank Holidays, so I have created a column that identifies If the date is Sun, Mon, Tues etc etc and also another column for if the date mentioned is a bank holiday (yes/no is returned)

Is there an extention that could be added to the formulas you have created under Shift 1, 2 & 3 columns to exclude or Zero the hours in those shifts that relate to Sunday or bank holiday? The hours are already auto filled into columns "sunday" or "bank holiday"

Thank you
Joe

 

[BSALV]

like this ?

Map1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			0:00										my Holidays
2			8:00										vr 01-07-22
3			20:00										ma 04-07-22
4			0:00										do 07-07-22
5													zo 10-07-22
6													wo 13-07-22
7													za 16-07-22
8	date	special	start	end	shift1	shift2	shift3	Sunday holidays	total				di 19-07-22
9	vr 01-07-22	Holiday	6:30	5:30				23:00	23:00				vr 22-07-22
10	za 02-07-22		6:30	5:30	7:00	12:00	4:00		23:00				ma 25-07-22
11	zo 03-07-22	Sunday	6:30	5:30				23:00	23:00				do 28-07-22
12	ma 04-07-22	Holiday	6:30	5:30				23:00	23:00				zo 31-07-22
13	di 05-07-22		6:30	5:30	7:00	12:00	4:00		23:00				wo 03-08-22
14	wo 06-07-22		6:30	5:30	7:00	12:00	4:00		23:00				za 06-08-22
15	do 07-07-22	Holiday	6:30	5:30				23:00	23:00				di 09-08-22
16	vr 08-07-22		6:30	5:30	7:00	12:00	4:00		23:00				vr 12-08-22
17	za 09-07-22		6:30	5:30	7:00	12:00	4:00		23:00				ma 15-08-22
18	zo 10-07-22	Sunday	6:30	5:30				23:00	23:00				do 18-08-22
19	ma 11-07-22		6:30	5:30	7:00	12:00	4:00		23:00				zo 21-08-22
20	di 12-07-22		6:30	5:30	7:00	12:00	4:00		23:00				wo 24-08-22
21	wo 13-07-22	Holiday	6:30	5:30				23:00	23:00				za 27-08-22
22	do 14-07-22		6:30	5:30	7:00	12:00	4:00		23:00				di 30-08-22
23	vr 15-07-22		6:30	5:30	7:00	12:00	4:00		23:00				vr 02-09-22
24	za 16-07-22	Holiday	6:30	5:30				23:00	23:00				ma 05-09-22
25	zo 17-07-22	Sunday	6:30	5:30				23:00	23:00				do 08-09-22
26	ma 18-07-22		6:30	5:30	7:00	12:00	4:00		23:00				zo 11-09-22
27	di 19-07-22	Holiday	6:30	5:30				23:00	23:00				wo 14-09-22
28	wo 20-07-22		6:30	5:30	7:00	12:00	4:00		23:00
29	do 21-07-22		6:30	5:30	7:00	12:00	4:00		23:00
30	vr 22-07-22	Holiday	6:30	5:30				23:00	23:00
31	za 23-07-22		6:30	5:30	7:00	12:00	4:00		23:00
32
Blad1

Cell Formulas
Range	Formula
B9:B31	B9	=IF(WEEKDAY(A9,2)=7,"Sunday",IF(ISNUMBER(MATCH(A9,TBL_Holidays[my Holidays],0)),"Holiday",""))
E9:E31	E9	=IF(AND([@special]="",COUNT($C9:$D9)=2),MAX(0,MIN($C$2,$D9+($D9<$C9))-MAX($C9,$C$1))+MAX(0,MIN($C$2+1,$D9+($D9<$C9))-1),"")
F9:F31	F9	=IF(AND([@special]="",COUNT($C9:$D9)=2),MAX(0,MIN($C$3,$D9+($D9<$C9))-MAX($C9,$C$2)),"")
G9:G31	G9	=IF(AND([@special]="",COUNT($C9:$D9)=2),MAX(0,MIN($C$4,$D9+($D9<$C9))-MAX($C9,$C$3)),"")
H9:H31	H9	=IF(AND([@special]<>"",COUNT($C9:$D9)=2),$D9-$C9+($D9<$C9),"")
I9:I31	I9	=IF(COUNT($C9:$D9)=2,$D9-$C9+($D9<$C9),"")
A10:A31	A10	=+A9+1
M3:M27	M3	=+M2+3

 

[JoeGraf10]

From a glance at the set up that's exactly how I have my set up!

I will re run tomorrow morning first thing and let you know how I get on!

Thank you Bsalv you Excel God

 

[JoeGraf10]

i tried to use your formulas but it wasnt quite working so i have re jigged it and now in Shift1 to Shift 3, like yesterday i need them to say 0:00 if there was a value in either the Sunday or Special Column. this is what is currently in there which is perfect for the splitting of the shifts:

=IF(COUNT($F15:$G15)=2,MAX(0,MIN($R$5,$G15+($G15<$F15))-MAX($F15,$R$4)),"")

was thinking of adding "somehow" (the above, then IF value in the Sunday column is greater than zero, return Zero hours if it is and if it isnt, do nothing & IF value in the Special column is greater than zero, return Zero hours if it is and if it isnt, do nothing)

what are your thoughts??

thank you

 

[BSALV]

for your 3 shift, the formula like in my E:G = if that column "Sunday or Special"=0 AND both start and stop are known then calculate the 3 shifts

=IF(AND([@Sunday or Special]=0,COUNT($C9:$D9)=2),MAX(0,.......

then the Sunday-column = IF([@Sunday or Special]="Sunday",[@Duration],0)
The Special-column = IF([@Sunday or Special]="Special",[@Duration],0)

if you have problems, add a link to your file

 

[JoeGraf10]

Amazing

Works a treat, thank you for all your help on this