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]

Okay, now it's not working. It said something about debug? And then it stopped. I tried to clear the contents and it popped up. Uh oh.

 

[cerfani]

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.

lol well at least you get to decide what is what. I just would suggest your hours data entry be separated from your sheet's functions and logic, which means to use a table that the functions reference. Then you have =SUM

 

[Joe4]

Which version of the code were you using when this happened, what exactly did you did that triggered that situation, and what is the exact error message?

 

[mrsbrannon]

The original version with the columns, which is exactly what I need, BTW.

I highlighted the data in the columns and did "clear contents" I don't know the exact error message. It was a Run-Time Error, but that's all I caught.

 

[Joe4]

"Clear Contents" does not cause any errors for me on that code, so I don't why you would have gotten an error from that.

To make sure things are all turned back on, run this short code:

Sub TurnEventsBackOn()
    Application.EnableEvents = True
End Sub

Then, replace the code you have this. If you get an error now, it will exit it more gracefully and make sure that events are turned back on. And it won't run when more than one cell is updated at a time (like a whole column):

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 column F
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    
'   See if entered value exceeds 40
    If Target.Value > 40 Then
        Application.EnableEvents = False
'       Update column G with overtime
        Target.Offset(0, 1).Value = Target.Value - 40
'       Update column F 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]

Okay that worked. Can I just replace F and G with J and K for my other two columns??

I have no idea what I did wrong that cause the error! Thank you!!

 

[Joe4]

Can I just replace F and G with J and K for my other two columns??

It is not quite that easy.

We need to understand the scope of this.
Is it just F2, F4, J2, and J3, or is it more cells than that?
If so, what does the next set look like?

 

[mrsbrannon]

It's just the same exact thing, just two different columns - Starting cell is J2 & K2, the columns are J & K, Regular Hours in J, OT Hours in K.

 

[Joe4]

OK, I am really not understanding your structure here.
What exactly is the pattern?
Before, you had Regular hours in row 2, and Overtime hours in row 3. So the Overtime hours seemed to be in the row below the Regular hours (vertical).
However, in my original code, I assumed Regular hours were in column F, and Overtime hours were in column G. So the Overtime hours were in the column to the right of Regular hours (horizontal).
And you said that worked (which it shouldn't if you have a vertical alignment and my code was written for a horizontal alignment).

Please clarify this for us.

 

[mrsbrannon]

That is exactly correct. I just track multiple times for billing purposes. So in the first to columns (F and G), I track regular hours. In J & K, I track billable hours. It's the same exact format of tracking, it's just duplicated for additional tracking (ridiculous, but it is what it is). Make sense?