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:
 
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?

It also says it's a circular reference. I assume since I'm entering the time in the same cell.
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
OK, I am confused about the structure of your sheet.
I assume that you have a range where you are entering the daily hours (8, 10, 8, 10, etc) and that you have some cell somewhere that is totally these individual days to get the total Hours (i.e. 40, 45, etc).
Is that correct?
If so, what exactly is the current formula in this total cell?
 
Upvote 0
OK, I am confused about the structure of your sheet.
I assume that you have a range where you are entering the daily hours (8, 10, 8, 10, etc) and that you have some cell somewhere that is totally these individual days to get the total Hours (i.e. 40, 45, etc).
Is that correct?
If so, what exactly is the current formula in this total cell?


No, I'm entering the daily hours in the same cell daily. So at the end of the week, under "regular hours" it shows 40 or whatever the number may be. I'm not totaling days in a separate range. That's way too many columns for what I'm doing. So this is not a "totals" cell, it's just THE cell. Does that clarify?
 
Last edited:
Upvote 0
OK, I think I understand what you are saying now.
So, let's say day one, you enter 8 in the cell.
The next day, if they work 10 hours, what do you do?
Do you just change the value to 18 hours?

So, are you saying then that if you enter 45 in the cell, you want it to automatically change to 40, and then move 5 to some Overtime cell?
VBA can certainly do that. If you just let me know the address of the cell you are entering hours in, and what the cell address of the Overtime cell is, I can come up with that code for you.
 
Last edited:
Upvote 0
I would suggest creating a new sheet that you enter these hours into each cell... for example each column can be a different employee and then you sum the columns... no need to edit the format of the current sheet but you would add a sheet to act as the database that your formula references. Your method is more prone to error since you are updating a formula and not just doing simple data entry.

With another sheet, your total hours just sums the entire column on that data sheet.

Code:
=SUM(HOURS_INPUT_SHEET!A:A)

then you can just add hours down the column and you can hide the sheet too if you want. You could even code a form to input the hours into the hidden sheet. It's up to you but you can make something much nicer that what the client expects. I would not build in inefficiency just because the person designing it is ignorant. I would educate them on how to do it right. Haha but i dont know the politics.
 
Last edited:
Upvote 0
Here is what the VBA code might look like. In this example, I assume column F is the column where you are entering the hours, and column G is Overtime hours.
Just right-click on the sheet tab name at the bottom of the sheet, select View Code, and paste this code in the resulting VB Editor Window:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim isect As Range
    Dim cell As Range

'   Trigger when value entered into column F
    Set isect = Intersect(Target, Range("F:F"))
    
'   Exit sub if nothing updated in column F
    If isect Is Nothing Then Exit Sub
    
'   Loop through all cells just updated in column F
    For Each cell In isect
'       See if entered value exceeds 40
        If cell.Value > 40 Then
            Application.EnableEvents = False
'           Update column G with overtime
            cell.Offset(0, 1).Value = cell.Value - 40
'           Update column F to 40
            cell.Value = 40
            Application.EnableEvents = True
        End If
    Next cell
    
End Sub
 
Upvote 0
OK, I think I understand what you are saying now.
So, let's say day one, you enter 8 in the cell.
The next day, if they work 10 hours, what do you do?
Do you just change the value to 18 hours?

So, are you saying then that if you enter 45 in the cell, you want it to automatically change to 40, and then move 5 to some Overtime cell?
VBA can certainly do that. If you just let me know the address of the cell you are entering hours in, and what the cell address of the Overtime cell is, I can come up with that code for you.


That would be amazing. Do you need the column titles or just the cell addresses? There's actually two columns tracking hours and two tracking overtime - so it's regular hours and regular OT and then billable hours and billable OT.

Cell's are F2 (Billable Hours), F3 (Billable OT Hours) & J2 (Regular Hours), J3 (Regular OT Hours)

Let me know if that makes sense. I am so ready to be able to write code. I feel as though it will solve most of my Excel problems.
 
Upvote 0
I would suggest creating a new sheet that you enter these hours into each cell... for example each column can be a different employee and then you sum the columns... no need to edit the format of the current sheet but you would add a sheet to act as the database that your formula references. Your method is more prone to error since you are updating a formula and not just doing simple data entry.

With another sheet, your total hours just sums the entire column on that data sheet.

Code:
=SUM(HOURS_INPUT_SHEET!A:A)

then you can just add hours down the column and you can hide the sheet too if you want. You could even code a form to input the hours into the hidden sheet. It's up to you but you can make something much nicer that what the client expects. I would not build in inefficiency just because the person designing it is ignorant. I would educate them on how to do it right. Haha but i dont know the politics.

Oh, trust me, I am already making it better than what it is. However, I'm also the one managing it so I'm trying to save myself as much double data entry as possible. AND I have to track weekly, until the end of the project which could be years.....It's fun. Lol.
 
Upvote 0
Here is what the VBA code might look like. In this example, I assume column F is the column where you are entering the hours, and column G is Overtime hours.
Just right-click on the sheet tab name at the bottom of the sheet, select View Code, and paste this code in the resulting VB Editor Window:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim isect As Range
    Dim cell As Range

'   Trigger when value entered into column F
    Set isect = Intersect(Target, Range("F:F"))
    
'   Exit sub if nothing updated in column F
    If isect Is Nothing Then Exit Sub
    
'   Loop through all cells just updated in column F
    For Each cell In isect
'       See if entered value exceeds 40
        If cell.Value > 40 Then
            Application.EnableEvents = False
'           Update column G with overtime
            cell.Offset(0, 1).Value = cell.Value - 40
'           Update column F to 40
            cell.Value = 40
            Application.EnableEvents = True
        End If
    Next cell
    
End Sub


THAT WORKED!!! I will copy the code and update for the other two columns. THANK YOU!!!!! I have GOT to learn this stuff. So amazing. Thanks for being so smart. :)
 
Last edited:
Upvote 0
Cell's are F2 (Billable Hours), F3 (Billable OT Hours) & J2 (Regular Hours), J3 (Regular OT Hours)
Here is a slightly different variation of the code to work specifically on changes to F2 and J2.
(Not sure if you really mean it will always be looking at row 2, or all of columns F and J).
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated at once
    If Target.Count > 1 Then Exit Sub

'   Only run in F2 or J2 updated
    If (Target.Address = "$F$2") Or (Target.Address = "$J$2") Then
'       See if entered value exceeds 40
        If Target.Value > 40 Then
            Application.EnableEvents = False
'           Update row below with overtime
            Target.Offset(1, 0).Value = Target.Value - 40
'           Update current cell to 40
            Target.Value = 40
            Application.EnableEvents = True
        End If
    End If
    
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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