I’m working on developing a payroll log of all non-exempt employees. Each person’s payroll record tracks:
-
The regular amount of normal 8 hours worked in any given day (Row 4),
-
Any overtime hours (ROW 5)
-
Any double time hours (Row 6).
-
Column L4 through L6 sums all hours in those rows.
Row 2 identifies the day of the week worked – all 7 days.
-
Row 7 identifies the number of hours in a day that is worked. This is the row where data is entered (D7 through J7).
-
If 8 hours (E7) is worked in a single day, the value of 8 is returned (E4) for that day in the 1st row. =IF(E7>8,8,E7)
If up to 12 hours is worked in a single day, the value of 8 is returned for that day in the 1st row (E4), and the remainder hours is returned in overtime row (E5) for that day. =IF(AND(E7>12, E4=8),4,E7-E4-E6) -
If anything greater than 12 hours is worked in a single day, the value of 8 is returned for that day in the 1st row (E4), the 4 hours of overtime is returned in the 2nd row (E5), and the remaining portion is relegated to the doubletime cell (E6). =IF(AND(E7>12,E4=8),E7-E4-E5,0)
I run into a problem when the regular hours reach 40, so when L4 sums the total number of regular hours (from left to right), it should be able to recognize that once it reaches 40 hours, stop summing. Is there a way to do tell Excel to stop summing once it reaches a certain number even if the range exceeds that number?
I also need to tell the formula in Row 4 (the regular hours), that if 40 hours has been reached (from left to right), to leave the cell blank, and allow the overtime cell beneath in row 5 to retrieve the value entered in on the 7th row.
I’m stumped at this point and I don’t know what to do. It would be easy if all employees work Monday through Friday
and then have the weekend off, but most work either Saturday or Sunday and then have 1 of the days of the work week off.
I hope I’ve made sense on what I is going on here. Please help! At this point, I just want to throw Excel out. :)
