Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

System Time correction

Status
Not open for further replies.

Benaway

Technical User
Feb 14, 2017
12
US
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.
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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.
 
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
 
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:

Code:
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.
 
Code:
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top