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, tracks any overtime hours, and tracks any double time hours.
Each record has 4 rows followed by a column for each day and 2 columns to sum all hours for each week in the pay period.
The first row tracks regular hours.
The 2nd row tracks any overtime hours.
The 3rd row tracks any doubletime hours.
The 4th row logs all hours worked for that day. This is the row where data is entered.
If 8 hours is worked in a single day, the value of 8 is returned for that day in the 1st row. =IF(E76>8,8,E76)
If up to 12 hours is worked in a single day, the value of 8 is returned for that day in the 1st row, and the remainder hours is returned in 2nd row for that day. =IF(AND(E76>12, E73=8),4,E76-E73-E75)
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, the 4 hours of overtime is returned in the 2nd row, and the remaining portion is relegated to the doubletime cell. =IF(AND(E76>12,E73=8),E76-E73-E74,0)
This works wonderfully…up until I get to the sixth and seventh days of the week. If someone works a normal Monday through Friday schedule, but work overtime on the 6th or 7th days, the formulas in the 1st row, return the number of hours worked in the regular hours, thus it adds to the total number of regular hours and pushes those hours past 40. I need a formula that tells Excel somehow that if 40 hours are worked, do not return the result of 40 in that cell and leave it as 0. So far, I have tried various different if statements and I’m not getting anywhere…I also keep coming across circular references. I’ve tried using AND and OR, which stump me even further but what I really need is a BUT statement but so such formula exist.
I’ve been trying to tell Excel if the first 5 days are greater than 40, then leave the regular hour cell as 0, but if not, then return the value of the hours worked for that day if it 8 hours or less, but trying to tell the IF function to return the value if false result if it is 8 or less is challenging.
In Column L, where I sum for the week the hours worked, that if the 1st five days = 40, then leave 40, but if not then add the 6th and 7th days, but I run aground when I try and tell the sixth and seventh days that if 40 hours have been worked, return the result of number of hours work (if it is 8 or less).
Spreadsheet is attached. I hope I’ve made sense on what I is going on here. Any help would be immeasurably appreciated.
Bookmarks