New to forum so Hi all,
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 7 hours 8am-3pm with break and finish time shows as 2:30pm.
Current setup is
Columns A=Staff name, B=7am-8am, C=8am-9am, D=9am-10am........I=2pm-3pm, J=Start time, K=Finish time, L=Lunch time, M=Total worked
Rows 1 Labels, i.e. B1=7am-8am, L1=Lunch time
Row 2=Staff name 1
Row 3=Staff name 2
Row 4=Staff name 3
etc
Formula in column J is
=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"),"")
Formula in column K is
=IFERROR(TEXT(DOLLARFR($M2+($M2>=7)*0.5+DOLLARDE(V3,60),60),"00.00"),"")
Column L, I still need the formula for this. Using LE and LL (Lunch Early and Lunch Late), LE will be the first half hour of the hour i.e. 12:00 and LL last half hour 12:30
Formula in column M is
=COUNTA(C3:T3)-(SUM(COUNTIF(C3:T3,{"LE","LL"})/2))
Entering number 1 in to cells C2-I2 except for G2 which would have LE, the total hours displays correctly showing 6.5. But the finish time is 14:30 and this should be 15:00 with a break at 12:00 making total hours paid 6.5
Im hoping someone can help me fix these time issues and maybe a formula for column L?
Thanks in advance.
Bookmarks