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

 

[Joe4]

See if this does what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo exit_err

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

'   Exit if update not in columns F or J
    If (Target.Column <> 6) And (Target.Column <> 10) Then Exit Sub
    
'   See if entered value exceeds 40
    If Target.Value > 40 Then
        Application.EnableEvents = False
'       Update column to right with overtime
        Target.Offset(0, 1).Value = Target.Value - 40
'       Update entered value to 40
        Target.Value = 40
        Application.EnableEvents = True
    End If
    
    Exit Sub
    
exit_err:

    Application.EnableEvents = True
    MsgBox Err.Number & ":" & Err.Description
    
End Sub

 

[mrsbrannon]

Oh my goodness. I finally slowed down for the day and read through this again. I'm SO SORRY! I misspoke. The columns are NOT identical in tracking. The first two (F - Regular & G - Overtime) are to count anything over 40 as OT. The SECOND set (J - Regular & K - Overtime) are to count anything over 50 as OT. SO SORRY!!

 

[Joe4]

Well, if there is something to be learned here, it is to be careful and thorough in posting the details of your question. You will get the answers you need a lot quicker if you do that (and cut down on a lot of the "back-and-forth" and code attempts that do not do what you want).

Here is the new version:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim hrmax As Long
    
    On Error GoTo exit_err

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

'   Exit if update not in columns F or J
    If (Target.Column <> 6) And (Target.Column <> 10) Then Exit Sub
    
'   See which column is being updated and select the appropriate max hours
    Select Case Target.Column
        Case 6
            hrmax = 40
        Case 10
            hrmax = 50
    End Select
        
'   See if entered value exceeds hour max
    If Target.Value > hrmax Then
        Application.EnableEvents = False
'       Update column to right with overtime
        Target.Offset(0, 1).Value = Target.Value - hrmax
'       Update entered value to hour max
        Target.Value = hrmax
        Application.EnableEvents = True
    End If
    
    Exit Sub
    
exit_err:

    Application.EnableEvents = True
    MsgBox Err.Number & ":" & Err.Description
    
End Sub

 

[mrsbrannon]

You are so correct. I apologize again. I will be more careful going forward with my questions. Thank you so much for your continued help. That worked beautifully.

 

[Joe4]

You are welcome.