INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

System Time correction

System Time correction

(OP)
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.

RE: System Time correction

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: System Time correction

(OP)
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.

RE: System Time correction

(OP)
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

RE: System Time correction

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

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:
Cells(Target.Row, 7).Value = MyNow

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: System Time correction

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close