If, greater than, remove difference

mrsbrannon

Board Regular
Joined
Mar 7, 2018
Messages
61
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!!!!!!!! :confused:
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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)
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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. :eek:
 
Upvote 0
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?
 
Last edited:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top