# Thread: If, greater than, remove difference

1. ## If, greater than, remove difference

Hello all,

Here is what I am trying to accomplish - Auto populate overtime hours to another column. Here is the breakdown in writing -

IF what is in this cell is GREATER THAN 40, REMOVE the amount that is GREATER and MOVE it to another column.

I currently have a "REGULAR TIME" and "OVERTIME" column.
In the OT column I did "=IFERROR(IF([Regular Hours]>40,[Regular Hours]-40,""),"")

While is produces the correct number, I'm still left with the original hours worked in the REGULAR column (say 45), PLUS 5 hours in the OT column for a total of 50 hours.

Is there a way to have it remove those 5 hours and place it in the OT column?

Thanks!!!!!!!!

2. ## Re: If, greater than, remove difference

you would need to use vba code to edit a cell's value based on a value that is in the same cell OR rename the column total hours and then you calculate the regular and overtime columns

If you think about it... you start with total hours... why would try to change it and then calculate total hours again later??

Overtime hours:

Code:
`=MAX(TOTAL-40,0)`
Regular Hours:

Code:
`=MIN(TOTAL,40)`

3. ## Re: If, greater than, remove difference

Is [Regular Hours] a calculation? If so, you can do this with two simple formulas:

For regular hours column:
=MIN([Regular Hours],40)

For OT hours column:
=MAX([Regular Hours]-40,0)

If you use these formulas, you would need to move anything. It is built right into the formulas.

4. ## Re: If, greater than, remove difference

Originally Posted by Joe4
Is [Regular Hours] a calculation? If so, you can do this with two simple formulas:

For regular hours column:
=MIN([Regular Hours],40)

For OT hours column:
=MAX([Regular Hours]-40,0)

If you use these formulas, you would need to move anything. It is built right into the formulas.
It's manual entry. I add them in each day so, 10+10+10, etc. Is that what you mean?

5. ## Re: If, greater than, remove difference

So, there are no hard-coded values over 40 hours? Any number showing over 40 is a calculated field?
If that is true, they I think you should be able to incorporate my formulas without having to use any VBA.

6. ## Re: If, greater than, remove difference

vba seems overkill when she already knows total hours... just calc the reg and overtime but...

It seems like OP is keeping a time sheet... there are tons of these templates already made online and many are free... unless you want to learn and make it yourself you can use an expert made timesheet to keep track of hours of employees... https://www.vertex42.com/ExcelTempla...-template.html

7. ## Re: If, greater than, remove difference

Originally Posted by Joe4
So, there are no hard-coded values over 40 hours? Any number showing over 40 is a calculated field?
If that is true, they I think you should be able to incorporate my formulas without having to use any VBA.

No hard-coded values, but the MIN formula overrides my time keeping - so I'm daily (sadly), having to key in each person's time, so each day I'm adding in the cell the day's totals (8+8+8+8, etc.)

Honestly, I think VBA would probably be what I need. What I want is once the total hits 40 hours (so say 5 days of 10 hours each day, added up like 10+10+10+10+10), the cell recognizes it and puts the remaining amount in the "OVERTIME" column.
I'm taking a VBA class, so maybe one day. Lol. I'm sure I'll find a manual work around until then.

8. ## Re: If, greater than, remove difference

Originally Posted by cerfani
vba seems overkill when she already knows total hours... just calc the reg and overtime but...

It seems like OP is keeping a time sheet... there are tons of these templates already made online and many are free... unless you want to learn and make it yourself you can use an expert made timesheet to keep track of hours of employees... https://www.vertex42.com/ExcelTempla...-template.html

I completely agree. Sadly, it's a client requirement and I have zero say-so, so manually it is.

9. ## Re: If, greater than, remove difference

Honestly, I think VBA would probably be what I need. What I want is once the total hits 40 hours (so say 5 days of 10 hours each day, added up like 10+10+10+10+10), the cell recognizes it and puts the remaining amount in the "OVERTIME" column.
That is what my two formulas already do, inherently!

Whatever you currently have for formulas for your "regular" and "overtime" hours, you should be able to replace those with formulas like mine that automatically cap Regular hours at 40 and put anything above that in Overtime.

Have you tried it?

10. ## Re: If, greater than, remove difference

Originally Posted by Joe4
That is what my two formulas already do, inherently!

Whatever you currently have for formulas for your "regular" and "overtime" hours, you should be able to replace those with formulas like mine that automatically cap Regular hours at 40 and put anything above that in Overtime.

Have you tried it?
Yes, sorry I stated what it did above, but I guess I didn't do it well.

When I do the MIN formula in the "Regular Hours" column, when I enter my time, it overrides the formula. I'm going into each cell and adding up the totals. Is there a way to format the column and lock that in?