OGroteKoning
Honorary Master
I am struggling to create formulas for this scenarion. Help would be appreciated.
It was agreed that XYZ company would be compensated for their staff on an actual basis. XYZ claimed the hours as per the green block. The correct calculation for the hours are in the red block (hand calculated - there could be some errors). The principle of payment is that XYZ would be compensated for 160 normal hours and 54 overtime hours per month. All hours worked over and above this agreement will be compensated at the agreed overtime rates:
Normal hours - Rate x 1.0
Type 1 OT hours - Rate x 1.5
Type 2 OT hours - Rate x 2.0
Type 3 OT hours - Rate x 2.5
Type 4 OT hours - Rate x 1.33
The principle is that XYZ has to "fill in" the 160 and 54 hours before any other overtime hours can be claimed.
Jack (Line 4) has worked all the alotted normal hours and more than his alotted overtime hours, therefor all hours claimed would be acceptable.
John (line 5) has not worked his full alotted normal hours and therefor some of his overtime hours must "fill in" the norml hours first, thereby reducing his OT hours.
Anne (Line 10) has not worked her full alotted normal hours and therefor forfeit all overtime hours.
Hours forfeited will start with the most expensive hours.
I need a formula for each of the columns F, G, H and I.
It was agreed that XYZ company would be compensated for their staff on an actual basis. XYZ claimed the hours as per the green block. The correct calculation for the hours are in the red block (hand calculated - there could be some errors). The principle of payment is that XYZ would be compensated for 160 normal hours and 54 overtime hours per month. All hours worked over and above this agreement will be compensated at the agreed overtime rates:
Normal hours - Rate x 1.0
Type 1 OT hours - Rate x 1.5
Type 2 OT hours - Rate x 2.0
Type 3 OT hours - Rate x 2.5
Type 4 OT hours - Rate x 1.33
The principle is that XYZ has to "fill in" the 160 and 54 hours before any other overtime hours can be claimed.
Jack (Line 4) has worked all the alotted normal hours and more than his alotted overtime hours, therefor all hours claimed would be acceptable.
John (line 5) has not worked his full alotted normal hours and therefor some of his overtime hours must "fill in" the norml hours first, thereby reducing his OT hours.
Anne (Line 10) has not worked her full alotted normal hours and therefor forfeit all overtime hours.
Hours forfeited will start with the most expensive hours.
I need a formula for each of the columns F, G, H and I.
