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
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
