I am trying to revise our timesheet to fit a third shift (graveyards). What I have now works for what we need as long as it is within a calendar day. If it spans two days, then it errors out.
The sticky part is this - lunches are tracked and a portion of the lunch is paid, provided it meets time requirements. Shifts lasting 10.5 hours or more have 15 minutes of their lunch paid; shifts lasting less than 10.5 hours have 10 minutes of their lunch paid.
Time logged is shift start, lunch start, lunch end, and shift end. This is what I currently have that works for morning/afternoon same-day shifts:
=IF(COUNT(G7,G10)<>2,"",IF(COUNT(G8:G9)<>2,IF(G10=0,1,G10)-G7,(G8-G7)+(IF(G10=0,1,G10)-G9)+IF(G9-G8>=(74/1440),0,IF((IF(G10=0,1,G10)-G7)*24>=10.5,(15/1440),(10/1440)))))
I've also attached the portion of the spreadsheet I am currently working with. Any help would be greatly appreciated. Thank you in advance for your thoughts.
Bookmarks