Rota start/finish times including a break

[~TaC~]

Hi,

I have an ongoing project where I fill in cells of scheduled work times for staff that results in displaying their start and finish times, sometimes over 7 hours and always 8 hours or more this will include a scheduled unpaid break so this time needs to be deducted. My current formula does all of this but sometimes when deducting the break the finish time is incorrectly adjusted. For example, staff member works 8 hours and takes half hour break, 8am-4pm, this displays correctly. Using same formula, they work 6 hours 8am-2pm no break and finish time shows as 1:30pm.

Here's how I have it set up.

Column A=staff name, B=contracted hours, C=5am-6am, D=6am-7am.... up to T=10pm-11pm, U=empty space, V=Start time, W=Finish time, X=Lunch break, Y=Hours worked

Row 3=Staff 1, 4=Staff 2 etc.

Formula in V for start time is
=IFERROR(TEXT(DOLLARFR(MATCH(TRUE,INDEX($C3:$T3<>"",,),0)+LEFT($C$2,FIND("-",$C$2)-1)-INDEX($C3:$T3,MATCH(TRUE,INDEX($C3:$T3<>"",,),0)),60),"00.00"),"")

Formula in W for finish time is
=IFERROR(TEXT(DOLLARFR($Y3+($Y3>=6)*0.5+DOLLARDE(V3,60),60),"00.00"),"")

Formula in Y for hours worked is
=IF(A3="","",COUNTA(C3:T3)-(SUM(COUNTIF(C3:T3,{"LE","LL"})/2))) where LE & LL represent Lunch Early & Lunch Late meaning the first or last half hour of the hour (this is a separate project where I then want to display the lunch break time).

I'm pretty much a novice in doing this and have had help to get this far so any Excel jargon may confuse me but any help is much appreciated

 

[mole999]

have you tried testing for the length of shift and only applying the break when shift is greater than X

 

[~TaC~]

Current setup

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		7-8	8-9	9-10	10-11	11-12	12-1	1-2	2-3	Start	Finish	Lunch	Total
2	Staff 1	1	1	1	1	1	LE	1	1	07.00	15.00		7.5
3	Staff 2			1	1	1	1	1		09.00	14.00		5
4	Staff 3	1	1	1	1	1	LL	1	1	07.00	15.00		7.5
5	Staff 4		1	1	1	LL	1	1	1	08.00	14.30		6.5

<tbody>
</tbody>

J2 =IFERROR(TEXT(DOLLARFR(MATCH(TRUE,INDEX($B2:$I2<>"",,),0)+LEFT($B$1,FIND("-",$B$1)-1)-INDEX($B2:$I2,MATCH(TRUE,INDEX($B2:$I2<>"",,),0)),60),"00.00"),"")

K2 =IFERROR(TEXT(DOLLARFR($M2+($M2>=7)*0.5+DOLLARDE(J2,60),60),"00.00"),"")

M2 =COUNTA(B2:I2)-(SUM(COUNTIF(B2:I2,{"LE","LL"})/2)))

J2 works fine for all start times in column J
K2 displays incorrect finish time for hours that are less than 8 per day (K5)
M2 counts the total number of paid hours worked in a day deducting the break time, where LE represents Lunch Early (first half hour of the hour) and LL represents Lunch Late (last half hour of the hour)

I currently have no formula for L2 to show the time of break.

This is what I am hoping to achieve

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		7-8	8-9	9-10	10-11	11-12	12-1	1-2	2-3	Start	Finish	Lunch	Total
2	Staff 1	1	1	1	1	1	LE	1	1	07.00	15.00	12.00	7.5
3	Staff 2			1	1	1	1	1		09.00	14.00		5
4	Staff 3	1	1	1	1	1	LL	1	1	07.00	15.00	12.30	7.5
5	Staff 4		1	1	1	LL	1	1	1	08.00	15.00	11.30	6.5

<tbody>
</tbody>

I'm not sure how your suggestion would fit in to this situation