Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to auto convert a time stamps to Eastern Standard T
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to auto convert a time stamps to Eastern Standard T
On Wed, 30 Jul 2008 16:38:01 -0700, DR wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to auto convert a time stamps to Eastern Standa
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 "Ron Rosenfeld" wrote: On Wed, 30 Jul 2008 16:38:01 -0700, DR wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to auto convert a time stamps to Eastern Standa
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 ******************* "DR" wrote in message ... 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 "Ron Rosenfeld" wrote: On Wed, 30 Jul 2008 16:38:01 -0700, DR wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to auto convert a time stamps to Eastern Standa
On Thu, 31 Jul 2008 00:31:24 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to auto convert a time stamps to Eastern Standa
On Wed, 30 Jul 2008 18:20:00 -0700, DR wrote:
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to auto convert a time stamps to Eastern Standa
On Thu, 31 Jul 2008 10:28:53 -0400, Ron Rosenfeld
wrote: On Thu, 31 Jul 2008 00:31:24 -0400, "Rick Rothstein \(MVP - VB\)" wrote: 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 OK, I found more info he http://www.cpearson.com/excel/TimeZo...lightTime.aspx that, in particular, addresses my concerns about DST. --ron |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to auto convert a time stamps to Eastern Standa
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/micro...8c0fef23dd1b89 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to auto convert a time stamps to Eastern Standa
On Thu, 31 Jul 2008 14:37:44 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: 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/micro...8c0fef23dd1b89 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/apiv...viewereng.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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to auto convert a time stamps to Eastern Standa
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 "Ron Rosenfeld" wrote: On Wed, 30 Jul 2008 18:20:00 -0700, DR wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to auto convert a time stamps to Eastern Standa
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/apiv...viewereng.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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to auto convert a time stamps to Eastern Standa
Hey guys,
I tried to drop that code you'd put together, but it didn't work. Not sure if I'm missing something. "Ron Rosenfeld" wrote: On Wed, 30 Jul 2008 18:20:00 -0700, DR wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to auto convert a time stamps to Eastern Standa
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 "DR" wrote in message ... Hey guys, I tried to drop that code you'd put together, but it didn't work. Not sure if I'm missing something. "Ron Rosenfeld" wrote: On Wed, 30 Jul 2008 18:20:00 -0700, DR wrote: 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to auto convert a time stamps to Eastern Standa
On Fri, 1 Aug 2008 15:07:54 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: 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/apiv...viewereng.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 Thank you for that information, Rick. I'll have a look when I get back home (out of town now). --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert standard time to 30 min increments? | Excel Discussion (Misc queries) | |||
Convert Time Decimal to standard time. | Excel Programming | |||
how do i convert standard time format into seconds? | Excel Discussion (Misc queries) | |||
In Excel, I want to convert Eastern time to pacific time | Excel Discussion (Misc queries) |