Is there a way to auto convert a time stamps to Eastern Standard T

[DR]

I am working with with various time stamps on a log sheet.
I'm handicapped by my limited proficiency with VBA and other UDFs.

What I'm hoping to have is a function that will convert a time stamp to
EST, as opposed to being presented in whatever zone it is logged in.

So, if a person in California time stamps 12:05 PM, it will automatically
add 3 hours when off DST or 4 hours when on DST.

I have a sneaking suspicion I will be at this for a while, just to learn
what I need to know to move forward with it..

Any help is appreciated!

 

[Ron Rosenfeld]

I am working with with various time stamps on a log sheet.
I'm handicapped by my limited proficiency with VBA and other UDFs.

What I'm hoping to have is a function that will convert a time stamp to
EST, as opposed to being presented in whatever zone it is logged in.

So, if a person in California time stamps 12:05 PM, it will automatically
add 3 hours when off DST or 4 hours when on DST.

I have a sneaking suspicion I will be at this for a while, just to learn
what I need to know to move forward with it..

Any help is appreciated!

It depends on how the date/time stamp is presented.

But, in general, you would add/subtract the appropriate number of hours
depending on the location and whether or not daylight savings time is in
effect. So you need to be able to identify the time zone used on the log in
sheet.

In general, it might be simpler to first convert all times to UTC. You could
then convert that to display in whatever TZ you chose.
--ron

 

[DR]

Hey Ron,

This is the macros I'm working with.
I'd like to have it such that the local time zone is either gotten from a
user selected cell, or by a get_time_zone function..

Any clue??
Anyone?
Beuller?


Sub InsertTime()
'
' InsertTime Macro
' Inserts the current time in the selected cell.
'
' Keyboard Shortcut: Ctrl+z
'
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

 

[Rick Rothstein \(MVP - VB\)]

First, while I recognize it isn't putting the value you actually want into
the selected cell, I just thought I should point out the the macro you
posted can be simplified greatly to this...

Sub InsertTime()
ActiveCell.Value = Now
End Sub

where we just assign VB's Now function to the active cell. Okay, now with
that said, let's answer your question...

It has been a long, long time since I worked out the original details behind
the base code for the following, but, as I remember it, I believe the
following will do what you want (convert a user's local time to the time on
the East Coast of the US and I'm pretty sure it takes Daylight Savings Time
into account). Copy/paste the code following my signature into a Module. To
use it, you would simply call the EastCoastTime function (without
parameters) and it should work automatically. For example, this macro should
convert the current user's date/time into the date/time on the East Coast of
the US and then assign it to the selected cell...

Sub InsertEastCoastTime()
ActiveCell.Value = EastCoastTime
End Sub

Rick

'******************* START OF MODULE CODE *******************
Declare Function GetTimeZoneInformation Lib "kernel32" _
(lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Type TIME_ZONE_INFORMATION
Bias As Long
StandardName As String * 64
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName As String * 64
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type

Function EastCoastTime() As Date
Dim TZ As TIME_ZONE_INFORMATION
Dim TimeZoneBias As Integer
GetTimeZoneInformation TZ
TimeZoneBias = TZ.Bias \ 60
EastCoastTime = DateAdd("h", TimeZoneBias - 5, Now)
End Function
'******************* END OF MODULE CODE *******************

 

[Ron Rosenfeld]

It has been a long, long time since I worked out the original details behind
the base code for the following, but, as I remember it, I believe the
following will do what you want (convert a user's local time to the time on
the East Coast of the US and I'm pretty sure it takes Daylight Savings Time
into account). Copy/paste the code following my signature into a Module. To
use it, you would simply call the EastCoastTime function (without
parameters) and it should work automatically. For example, this macro should
convert the current user's date/time into the date/time on the East Coast of
the US and then assign it to the selected cell...

Nice routine, Rick.

A few comments:

1. Your routine apparently cannot be just pasted below an existing sub. If I
do that, I get an error message about what cannot be allowed after end sub.

It needs to be at the top, or probably in a different module.

2. Examining the TIME_ZONE_INFORMATION value, it seems to me that this
conversion to East Coast time will only work if both locale's have the same
daylight savings time bias. It seems as if DaylightDate and StandardDate
contain the month/day/hour of conversion from one to the other.

I can see this being an issue if one wanted to convert to UTC.
--ron

 

[Ron Rosenfeld]

Hey Ron,

This is the macros I'm working with.
I'd like to have it such that the local time zone is either gotten from a
user selected cell, or by a get_time_zone function..

Any clue??
Anyone?
Beuller?


Sub InsertTime()
'
' InsertTime Macro
' Inserts the current time in the selected cell.
'
' Keyboard Shortcut: Ctrl+z
'
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

Does Rick's solution point you in the right direction?
--ron

 

[Ron Rosenfeld]

Nice routine, Rick.

A few comments:

1. Your routine apparently cannot be just pasted below an existing sub. If I
do that, I get an error message about what cannot be allowed after end sub.

It needs to be at the top, or probably in a different module.

2. Examining the TIME_ZONE_INFORMATION value, it seems to me that this
conversion to East Coast time will only work if both locale's have the same
daylight savings time bias. It seems as if DaylightDate and StandardDate
contain the month/day/hour of conversion from one to the other.

I can see this being an issue if one wanted to convert to UTC.
--ron

OK, I found more info here:

http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx

that, in particular, addresses my concerns about DST.
--ron

 

[Rick Rothstein \(MVP - VB\)]

A few comments:

1. Your routine apparently cannot be just pasted below an existing sub.
If I
do that, I get an error message about what cannot be allowed after end
sub.

It needs to be at the top, or probably in a different module.

I think the Declare and Type declarations are the problem... I believe they
need to be placed before any local procedure declarations.

2. Examining the TIME_ZONE_INFORMATION value, it seems to me that this
conversion to East Coast time will only work if both locale's have the
same
daylight savings time bias. It seems as if DaylightDate and StandardDate
contain the month/day/hour of conversion from one to the other.

I can see this being an issue if one wanted to convert to UTC.

I couldn't remember if there was a problem with the daylight savings time or
not. I posted the original code it was derived from back in 1999 so what I
remember about my investigations back then are a little more than sketchy;
see this link for the original post and function...

http://groups.google.com/group/micr...3dd1b89?hl=en&lnk=st&q=&#doc_b78c0fef23dd1b89

My recollection was that the code handles the user's DST setting
automatically, but that seems not to be the case. I changed the offset
from -5 to 0 (to get UTC/GMT times) and it is an hour off (we are observing
DST right now, so that has to be the missing hour). I was going to
investigate this in more detail, but the link you provided in your other
message to the work Chip has done means I no longer have to do so. One of
these days, I'll update my old files with some of the new insights I get
from Chip's website (it looks like I was on the right track, just missing
some minor details).

Rick

 

[Ron Rosenfeld]

I think the Declare and Type declarations are the problem... I believe they
need to be placed before any local procedure declarations.


I couldn't remember if there was a problem with the daylight savings time or
not. I posted the original code it was derived from back in 1999 so what I
remember about my investigations back then are a little more than sketchy;
see this link for the original post and function...

http://groups.google.com/group/micr...3dd1b89?hl=en&lnk=st&q=&#doc_b78c0fef23dd1b89

My recollection was that the code handles the user's DST setting
automatically, but that seems not to be the case. I changed the offset
from -5 to 0 (to get UTC/GMT times) and it is an hour off (we are observing
DST right now, so that has to be the missing hour). I was going to
investigate this in more detail, but the link you provided in your other
message to the work Chip has done means I no longer have to do so. One of
these days, I'll update my old files with some of the new insights I get
from Chip's website (it looks like I was on the right track, just missing
some minor details).

Rick

You probably have appropriate tools for handling API's. But I've never worked
with them. Googling around, I found this:

http://www.activevb.de/rubriken/apiviewer/index-apiviewereng.html

which is said to be (or have been at the time of its creation in 2004) more
inclusive than the MS supplied API viewer.

Interesting stuff.

Do you have anything more recent (that will run without Visual Basic having
been installed)?
--ron

 

[DR]

Hey guys,

It does point me in the right direction...

Sorry for the delay in responding. I had to convert yesterday and it had me
unable to access my MS products..
I'll test it today.

The issue of DST may turn out to be an important one, since the logs will
need to be reconsiled with server data and the less variance, naturally, the
better..


Thanks,
DR

 

[Rick Rothstein \(MVP - VB\)]

You probably have appropriate tools for handling API's. But I've never

worked
with them. Googling around, I found this:

http://www.activevb.de/rubriken/apiviewer/index-apiviewereng.html

which is said to be (or have been at the time of its creation in 2004)
more
inclusive than the MS supplied API viewer.

This is the one I use most...

http://allapi.mentalis.org/agnet/apiguide.shtml

it is older (and less inclusive of "newer" API functions), but the examples
are worth their weight in gold.

Interesting stuff.

More than interesting is the sheer power available using the API functions.
That is because ultimately, anything you do program-wise on a Windows system
ends up making calls to the underlying API functions; so almost any magic
you see another program doing can probably be duplicated in VB. I say
"almost" and "probably" because VB is a single-thread language, so there are
a lot of API functions not usable within it (and I'm sure there are other
restricted groups of API functions also not available in it). The big
problem with the API functions, though, is there is no really good
documentation on using it (there is a dictionary sized book by Appleman from
a few years ago, but it can be daunting to read/follow) and the set up of
the individual API functions is cumbersome at best (read that as highly user
unfriendly).

Do you have anything more recent (that will run without Visual Basic
having
been installed)?

The API-Guide I posted the link above for runs as a stand alone application.

Rick

 

[DR]

Hey guys,

I tried to drop that code you'd put together, but it didn't work.

Not sure if I'm missing something.

 

[Rick Rothstein \(MVP - VB\)]

Describe "didn't work" in more detail please.

And which code did you "drop"... mine? If so, did you follow all the
directions I posted (specifically the part about putting it in a Module)?

Rick

 

[Ron Rosenfeld]

This is the one I use most...

http://allapi.mentalis.org/agnet/apiguide.shtml

it is older (and less inclusive of "newer" API functions), but the examples
are worth their weight in gold.


More than interesting is the sheer power available using the API functions.
That is because ultimately, anything you do program-wise on a Windows system
ends up making calls to the underlying API functions; so almost any magic
you see another program doing can probably be duplicated in VB. I say
"almost" and "probably" because VB is a single-thread language, so there are
a lot of API functions not usable within it (and I'm sure there are other
restricted groups of API functions also not available in it). The big
problem with the API functions, though, is there is no really good
documentation on using it (there is a dictionary sized book by Appleman from
a few years ago, but it can be daunting to read/follow) and the set up of
the individual API functions is cumbersome at best (read that as highly user
unfriendly).


The API-Guide I posted the link above for runs as a stand alone application.

Rick

Thank you for that information, Rick. I'll have a look when I get back home
(out of town now).
--ron