System Time correction

[Benaway]

Our computers are controlled by a central server on pacific time which is lock down and we can not change. . We are on central time which is a one hour difference. When we make spread sheets in excel, is always looks at the system time to calculate dates and time, which are incorrect for our area. I need a simple VBA code to basically subtract 1 hour from the system time to reflect the proper times on our excel spreadsheets.

 

[SkipVought]

Hi,

Last time I checked, it was (west to east)...

Pacific, Mountain, Central...

TWO hour difference.

Anyhow add 2/24 to the Pacific Date/Time value to get Central time.

Of course, there might be a Standard to Daylight Saving time, which could result in the ONE hour difference (1/24).

Alternatively add TIME(2,0,0)

BTW, around midnight you mighr have a problem IF your value is Time ONLY rather than Date/Time.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Benaway]

Thanks, and you are correct. Do not know what I was thinking. I meant eastern time instead of pacific time. Yes, will need to include the date as well to keep things straight.

 

[Benaway]

The following is the VBA code I am using for date and time in Target.Row, 7.

What would need to be changed in it to subtract 1 hour from the now time:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Cells(5, "B").EntireColumn) Is Nothing Then
Intersect(Target.EntireRow, Range("E:F")).ClearContents
End If
If Target.Column = 2 Then
Application.EnableEvents = False
Cells(Target.Row, 7).Value = Now()
Application.EnableEvents = True

End If
End Sub

 

[Andrzejek]

You may, instead of:[tt]
Cells(Target.Row, 7).Value = Now()
[/tt]do
[tt]Cells(Target.Row, 7).Value = DateAdd("h", -1, Now)[/tt]

But I would establish a little Function to give me MyNow (or whatever you want to call it) and use it everywhere:

Public Function MyNow() As Date
MyNow = DateAdd("h", -1, Now)
End Function

So you would have:
[tt]Cells(Target.Row, 7).Value = MyNow[/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Cells(5, "B").EntireColumn) Is Nothing Then
      Application.EnableEvents = False
      Intersect(Target.EntireRow, Range("E:F")).ClearContents
   End If
   If Target.Column = 2 Then
      Application.EnableEvents = False
      Cells(Target.Row, 7).Value = Now() - (1/24)
   End If

   Application.EnableEvents = True
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]