Forum Discussion
Break down Work Hours into Overtime
I inherited a spreadsheet with a bunch of problems; most of which I have solved except this one which I get stuck on.
I need to take Hours worked and break them down into the Overtime hours in separate cells so:
A4 - Hours Worked (that number is gathered already - no calc needed)
A5 - how many of those Work hours were at Straight time?
-min hours is 8 (even if they worked less)
A6 - how many are 1.5x
- all hours from 8.1 to 12
A7. - how many at 2x
- all hours from12.1 to15
A8 - how many at 3x
- all hours from 15.1 and above
You can use the following formulas to break down the worked hours into different overtime categories based on your criteria:
Assuming that the total worked hours are in cell A4:
For Straight Time (up to 8 hours):
=MIN(8, A4)
For Time and a Half (8.1 to 12 hours):
=IF(A4>8, MIN(4, A4-8), 0)
For Double Time (12.1 to 15 hours):
=IF(A4>12, MIN(3, A4-12), 0)
For Triple Time (15.1 and above):
=IF(A4>15, A4-15, 0)
You can place these formulas in cells A5 to A8 to get the breakdown of the worked hours. Adjust the reference in the formulas accordingly based on where your total worked hours are located.
These formulas ensure that each category is calculated based on the specified criteria. The MIN function is used to limit the number of hours counted within each category.
Of Course you can nest these conditions within a single formula to achieve the desired result. Here's a nested formula that covers all the conditions:
=IF(A4<=8, A4, IF(A4<=12, 8, IF(A4<=15, 8 + (A4-12)*1.5, 8 + 3*1.5 + (A4-15)*2)))
This formula breaks down the worked hours into different overtime categories:
- Up to 8 hours: Straight Time
- More than 8 and up to 12 hours: Time and a Half
- More than 12 and up to 15 hours: Double Time
- More than 15 hours: Triple Time
You can place this formula in the cell where you want the total calculated, and it will give you the correct breakdown based on the specified criteria.
The text, steps and formulas was created with the help of AI, please don’t forget, the formulas are untested and don't know if the formulas could fit your plans.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
3 Replies
This 365 solution is over-elaborate but amused me!
= IF(hoursWorked > 0, LET( hours, MAX(hoursWorked, 8), upper, SORT(VSTACK(thresholds, hours)), lower, VSTACK(0, thresholds), hoursByBand, upper - lower, earnings, hoursByBand * OTRates * hourlyRate, headers, {"Rate","Hours","Amount"}, breakdown, HSTACK(rateText, hoursByBand, earnings), totals, HSTACK("Totals", SUM(hoursByBand), SUM(earnings)), output, VSTACK(headers, FILTER(breakdown, hoursByBand),totals), output ), {"Rate","Hours","Amount"} )You can use the following formulas to break down the worked hours into different overtime categories based on your criteria:
Assuming that the total worked hours are in cell A4:
For Straight Time (up to 8 hours):
=MIN(8, A4)
For Time and a Half (8.1 to 12 hours):
=IF(A4>8, MIN(4, A4-8), 0)
For Double Time (12.1 to 15 hours):
=IF(A4>12, MIN(3, A4-12), 0)
For Triple Time (15.1 and above):
=IF(A4>15, A4-15, 0)
You can place these formulas in cells A5 to A8 to get the breakdown of the worked hours. Adjust the reference in the formulas accordingly based on where your total worked hours are located.
These formulas ensure that each category is calculated based on the specified criteria. The MIN function is used to limit the number of hours counted within each category.
Of Course you can nest these conditions within a single formula to achieve the desired result. Here's a nested formula that covers all the conditions:
=IF(A4<=8, A4, IF(A4<=12, 8, IF(A4<=15, 8 + (A4-12)*1.5, 8 + 3*1.5 + (A4-15)*2)))
This formula breaks down the worked hours into different overtime categories:
- Up to 8 hours: Straight Time
- More than 8 and up to 12 hours: Time and a Half
- More than 12 and up to 15 hours: Double Time
- More than 15 hours: Triple Time
You can place this formula in the cell where you want the total calculated, and it will give you the correct breakdown based on the specified criteria.
The text, steps and formulas was created with the help of AI, please don’t forget, the formulas are untested and don't know if the formulas could fit your plans.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
