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.
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.
