Subtract X hours from entered value

[Anne Troy]

In a cell called offtime, I will enter 2 or 3, depending on the time of year. This indicates that I (on the east coast) am 2 or 3 hours later than the time in Phoenix AZ.

In my worksheet column K, I will enter the east coast time. I want it to auto-change my entry by deducting the value in the cell called offtime.

So far, I just have:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler:
    
    If Not Application.Intersect(Me.Range("K4:K50"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = True Then
            Application.EnableEvents = False
            Target.Value = Target - Range("offhours")
            
            Application.EnableEvents = True
        End If
    End If
ErrHandler:
    Application.EnableEvents = True
End Sub

Any help GREATLY appreciated!

 

[JoeMo]

Assuming you're entering times in col K and that your named range is "offhours" (not "offtime"), see if this does what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
    On Error GoTo ErrHandler:
    If Not Application.Intersect(Me.Range("K4:K50"), Target) Is Nothing Then
        For Each c In Intersect(Me.Range("K4:K50"), Target)
            If IsNumeric(Target.Value) = True Then
                Application.EnableEvents = False
                Target.Value = Target - TimeSerial(Range("offhours"), 0, 0)
                
                Application.EnableEvents = True
            End If
       Next c
    End If
ErrHandler:
    Application.EnableEvents = True
End Sub

 

[Anne Troy]

Joe, thanks. I gave that a shot. It does the same as mine. Nothing. LOL

 

[jim may]

Format your Column K as Time: 1:30 PM

Enter this code into your Sheet Code Module

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Columns("K:K"), Target) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
OV = Target.Value  'The Orig Time(EC) entered.
Application.Undo
RV = OV - (Range("Offhours") / 24)   'The RV (Revised time)
Target.Value = RV
Application.EnableEvents = True
End Sub

Excel 2010#DAE7F5 " />#DAE7F5 ;text-align: center;color: #161120">

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	OP:										Enter Time (Below)		2	<< # of Hours to Auto-Deduct from Your Column K Entry Time
2	In a cell called offhours, I will enter 2 or 3, depending on the time of year. This indicates that										1:00 PM
3	I (on the east coast) am 2 or 3 hours later than the time in Phoenix AZ.										2:00 PM			Change Cell M1 as Needed
4	In my worksheet column K, I will enter the east coast time. I want it to auto-change										2:30 PM
5	my entry by deducting the value in the cell called offhours.										3:15 PM
6											3:25 PM
7											11:30 AM
8
9
10
11
12

<tbody>
</tbody>

#DAE7F5 ;color: #161120">Sheet1​

 

[Anne Troy]

This does nothing as well, Jim. Very strange. I think I'm going to enter it into one range, have a formula in another, and auto-copy the value. This is nuts. Thanks so much!

 

[Phuoc]

Or try:

Target.Value = (Target.Value - Range("offhours") / 24) - (Target.Value < Range("offhours") / 24)

 

[jim may]

Does NOTHING? You need to "step-through" each line of code ( using the F8 function key) and audit what's going on as you progress. Do so and write back furnishing your findings!! Jim

 

[Anne Troy]

Does NOTHING? You need to "step-through" each line of code ( using the F8 function key) and audit what's going on as you progress. Do so and write back furnishing your findings!! Jim

How do I step through code that runs on a worksheet_change? I just get object not defined errors.
It acts like it's doing something for the briefest second, but the value that comes back is the same as what I type in.
Step Into / F8 do nothing.

 

[jim may]

Go into your VB Editor; With your Sheet Code Visible -- adjacent to code-line 5

Application.EnableEvents = False​

In the left margin (the thin grey strip) hover over it and then either Left click inside
or press the F9 key - that should insert a red/maroon bullet/ball (indicating you have just assigned a breakpoint) which means when the code is triggered it will RUN up to this Line and STOP/HALT.. Go into a suspense mode. Back at your worksheet -- make a change which will fire or trigger the code and it should run the 1st 4 lines and stop @ Line 5 (the breakpoint line).
From this point on you can step thru one line at a time by pressing the F8, F8, F8 executing code lines 5,6 and 7 to the end (last code line End Sub).
During this operation you should see what the code is doing or not doing. REMOVE the Breakpoint before Saving or exiting either by left-clicking on the red/maroon dot/ball or with the cursor over the dot/ball press the F9 key. Hope this helps. Jim

​

 

[Tetra201]

In a cell called offtime, I will enter 2 or 3, depending on the time of year. This indicates that I (on the east coast) am 2 or 3 hours later than the time in Phoenix AZ...

When you are done with fixing your Worksheet_Change sub, you may want to put the following formula in the "offtime" (or "offhours"?) cell to avoid manually entering 2 or 3:

<workday.intl((year(today())&"-11-1")-1,1,"1111110")+1 12)
=2+AND(NOW()>=WORKDAY.INTL((YEAR(TODAY())&"-3-1")-1,2,"1111110")+1/12,NOW() < WORKDAY.INTL((YEAR(TODAY())&"-11-1")-1,1,"1111110")+1/12)

Or you may want to implement these calculations in your Worksheet_Change sub.</workday.intl((year(today())&"-11-1")-1,1,"1111110")+1>