Subtract X hours from entered value

Anne Troy

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,632
Office Version
  1. 365
Platform
  1. Windows
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:

Code: 
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!
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Assuming you're entering times in col K and that your named range is "offhours" (not "offtime"), see if this does what you want.
Code: 
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
 
Upvote 0
Joe, thanks. I gave that a shot. It does the same as mine. Nothing. LOL
 
Upvote 0
Format your Column K as Time: 1:30 PM

Enter this code into your Sheet Code Module

Code: 
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">
ABCDEFGHIJKLMN
OP:<< # of Hours to Auto-Deduct from Your Column K Entry Time
In a cell called offhours, 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.Change Cell M1 as Needed
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 offhours.

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Enter Time
(Below)[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] , align: center"]2[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]1:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]2:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]2:30 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]3:15 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]3:25 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]11:30 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
#DAE7F5 ;color: #161120">Sheet1​
 
Last edited:
Upvote 0
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!
 
Upvote 0
Or try:

Target.Value = (Target.Value - Range("offhours") / 24) - (Target.Value < Range("offhours") / 24)
 
Upvote 0
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
 
Last edited:
Upvote 0
jim may said:
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
Click to expand...

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.
 
Upvote 0
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

​
 
Last edited:
Upvote 0
Anne Troy said:
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...
Click to expand...
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>
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
color cell in red, when entering empty time value
Replies
2
Views
171
robertvdb
R
T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
306
tixarah
T
M
  • Question Question
Prevent worksheet change from triggering when similar macro is executed?
Replies
1
Views
106
AlphaFrog
AlphaFrog
R
  • Solved
values in column C must be larger than in B
Replies
4
Views
340
robertvdb
R
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
752
sspatriots
S

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

Share this page

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