I am creating a work schedule for 12 employees in excel 2007. The schedule will contain “Time In”, “Time Out” and total hours worked for that day. And total hours worked for the particular week. I need a formula that will calculate the “Time In”, “Time Out” in a separate cell, say (A1=Time In, A2=Time out and A3=hours worked). And if the hours worked for that day are more than 5, then deduct a 30 minute lunch break, if less than 5 hours worked, then no lunch break taken. Then sum all the hours worked for that particular work week in a separate cell at the end of the schedule. See below sample:
SEE IMAGE: Attachment 134642
I have 2 formulas that work half way. Which is to say, that the first will work for the 3rd shift and will deduct the break but not for the 1st and 2nd shift. The other formula works for the 1st and 2nd shift but not for the 3rd. See below:
This formula works for 3rd shift =IF(B8+1-A8<1/24*5,B8+1-A8,B8+1-A8-1/48)
This formula works for the 1st and 2n shifts. =IF(B10-A10<1/24*5.6,B10-A10+(B10<A10),B10-A10-1/48)
Bookmarks