If, greater than, remove difference

[mrsbrannon]

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

 

[cerfani]

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:

=MAX(TOTAL-40,0)

Regular Hours:

=MIN(TOTAL,40)

 

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

 

[mrsbrannon]

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?

 

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

 

[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/ExcelTemplates/free-timesheet-template.html

 

[mrsbrannon]

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.

 

[mrsbrannon]

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/ExcelTemplates/free-timesheet-template.html

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

 

[Joe4]

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?

 

[mrsbrannon]

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?