Subtract Hours from Time Using VBA Change Event

[bemp87]

Hi Community - it's been a while since I did this, and I can't remember for the life of me how to add / subtract hours from time.

Here is a draft of the VBA code I have to perform expression:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range("D6") Then
     Target.Value = Target.Value - 2
Else
      ' Do Nothing
End If

End Sub

This isn't performing the expression that I would hope. Essentially I will have users that will enter time based on a 12 hour format in column D based on Central Standard Time. After the user has made the change / entry the event should trigger and subtract (2) hours from the users entered time to reflect the time it would be based on Pacific Standard Time. Not sure what exactly I am missing.

 

[mikerickson]

Its always best to enter time (and dates) in Excel serial time. And the cell will accept it as a date (if the user types is a space before AM or PM)

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range("D6") Then
     Application.EnableEvents = False
     Target.Value = Target.Value - TimeSerial(2,0,0)
     Target.Format = "h:mm AM/PM ""PT"""
     Application.EnableEvents = True
Else
      ' Do Nothing
End If

End Sub

 

[bemp87]

Thanks for the help! This is working as expected with one minor error. It seems there is an issue when calling the .Format property / method of the Target object. I'm getting the following error:

Here's the line of code when i run the debugger.

 

[mikerickson]

Should be Target.NumberFormat.

 

[bemp87]

Sorry for the late response, but thanks Mik- this works like a charm!