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

 

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

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

 

[Joe4]

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?

 

[mrsbrannon]

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?

 

[Joe4]

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.

 

[cerfani]

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.

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

 

[Joe4]

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:

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

 

[mrsbrannon]

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.

 

[mrsbrannon]

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.

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

 

[mrsbrannon]

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:

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.

 

[Joe4]

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

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