Forum Discussion
Solved
Automatic Addition, Numerical Limit and Overflow
I am creating an overtime report sheet for a crew ranging from 20 to 75 people. Rather than type up all of the hours for each individual, highlighting everyone's times and then using the sum feature ...
I believe this is what SergeiBaklan is suggesting...
Assuming you have headers in row 1 and your data begins in row 2:
In cell M2, you may put the formula:
=Min(L2,40)
In cell N2, you may put the formula:
=Max(0,M2-40)
In cell O2, you may put the formula:
=N2/2
Then of course copy this formula down as necessary.
It's easier to add one more column for total hours. If you type in it, let say, 55, in next column will be regular hours as =MIN(total, 40), in next overtime as =MAX(0, total-40)
SergeiBaklan
Sorry, I'm a little confused. Here is a bit more information.
Column A is just comprised of numbers to know the amount on the list.
Column B is Employee Names
Column C is Employee ID Numbers
Column D is their trade.
Columns E-K are Monday - Sunday
Column L is total hours for the week
Column M is Regular hours (≤40)
Column N is Overtime hours (The remainder of any number greater than 40 in column M)
Column O is Premium time (1/2 of Column N values)
I believe this is what SergeiBaklan is suggesting...
Assuming you have headers in row 1 and your data begins in row 2:
In cell M2, you may put the formula:
=Min(L2,40)
In cell N2, you may put the formula:
=Max(0,M2-40)
In cell O2, you may put the formula:
=N2/2
Then of course copy this formula down as necessary.
