Hi guys,
I'm helping my wife with a spreadsheet to calculate hours of work for her employees and I'm trying to create a formula with multiple conditions to calculate the correct overtime.
Basically I have columns for time in and time out, then a column that verifies the actual amount of hours worked (9) against the amount of hours an employee should work (8) and then deducts 1 hour for lunch.
Then I have 2 other columns with SUMIF functions, where the first calculates the amount of overtime like this: SUMIF('HOURS/DAY' ;">"&HOURS TO WORK;'HOURS/DAY'−8), which should ideally return a value of 0 hours (which it does). The second column calculates the hours owed if worked less than the prescribed 8 hours like this: SUMIF('HOURS/DAY';"<"&HOURS TO WORK;8−'HOURS/DAY'), which should ideally return the value if I deduct the 8 prescribed hours from the actual hours worked (again, which it does).
Where I get stuck is on the next column where I'm trying to calculate the overtime at 1.5 like usual, but when an employee owes her hours, the extra hours worked does not count as 1.5, but as a standard hour, until all the hours owed are worked back, then from 0 hours owed and onward, overtime gets calculated at 1.5
So what I have so far is something along the lines of this: IF(OT TOTAL<"0";OT TOTAL+BANK;IF(OT TOTAL 4≥"0";OT TOTAL+BANK 5×1.5;OT TOTAL 4−IOU))
I hope this makes sense.
So I am using nested IF functions to try and handle more than 1 condition. If the overtime total is <0, then only count the extra hours worked at 1. If =>0 then count the extra hours at 1.5. Else, if the hours worked is less than the prescribed 8 for the day, just deduct more hours from the IOU column.
Where I really get worried is where I also need to make sure that if an employee, for example, owes 2 hours and he works 3 extra for the day, only the first 2 (-2 up to 0) counts as x1, but the last of the 3 (0 up to 1) should count as x1.5. Is that even possible?
Thanking you in advance.
Great forum by the way
Regards
I'm helping my wife with a spreadsheet to calculate hours of work for her employees and I'm trying to create a formula with multiple conditions to calculate the correct overtime.
Basically I have columns for time in and time out, then a column that verifies the actual amount of hours worked (9) against the amount of hours an employee should work (8) and then deducts 1 hour for lunch.
Then I have 2 other columns with SUMIF functions, where the first calculates the amount of overtime like this: SUMIF('HOURS/DAY' ;">"&HOURS TO WORK;'HOURS/DAY'−8), which should ideally return a value of 0 hours (which it does). The second column calculates the hours owed if worked less than the prescribed 8 hours like this: SUMIF('HOURS/DAY';"<"&HOURS TO WORK;8−'HOURS/DAY'), which should ideally return the value if I deduct the 8 prescribed hours from the actual hours worked (again, which it does).
Where I get stuck is on the next column where I'm trying to calculate the overtime at 1.5 like usual, but when an employee owes her hours, the extra hours worked does not count as 1.5, but as a standard hour, until all the hours owed are worked back, then from 0 hours owed and onward, overtime gets calculated at 1.5
So what I have so far is something along the lines of this: IF(OT TOTAL<"0";OT TOTAL+BANK;IF(OT TOTAL 4≥"0";OT TOTAL+BANK 5×1.5;OT TOTAL 4−IOU))
I hope this makes sense.
So I am using nested IF functions to try and handle more than 1 condition. If the overtime total is <0, then only count the extra hours worked at 1. If =>0 then count the extra hours at 1.5. Else, if the hours worked is less than the prescribed 8 for the day, just deduct more hours from the IOU column.
Where I really get worried is where I also need to make sure that if an employee, for example, owes 2 hours and he works 3 extra for the day, only the first 2 (-2 up to 0) counts as x1, but the last of the 3 (0 up to 1) should count as x1.5. Is that even possible?
Thanking you in advance.
Great forum by the way
Regards