Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i check system time zone from excel?
I have a worksheet used by people across the globe and it involves time
functions. I have my calculations based on CST. However, I cannot expect every one to have there system clock set to CST. Is there any easy method to find out what timezone the system is set to? PS: Iam not a full time programmer. Please excuse if this is a silly question to be asked. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i check system time zone from excel?
I don't know if this is any help, but the following function returns GMT (UCT
or Zulu): Function gmt() As Date Application.Volatile dot = "." Set Serve = GetObject("winmgmts:\\" & dot & "\root\cimv2") Set Zones = Serve.ExecQuery("Select * From Win32_TimeZone") For Each zone In Zones intTimeZoneBias = zone.bias intDayLightBias = zone.DaylightBias Next gmt = Now() - (intTimeZoneBias - intDayLightBias) / (60 * 24) End Function In the worksheet you can compare GMT to NOW() to find out how many hours the local time reference is to GMT. -- Gary''s Student - gsnu200852 "Yuvi" wrote: I have a worksheet used by people across the globe and it involves time functions. I have my calculations based on CST. However, I cannot expect every one to have there system clock set to CST. Is there any easy method to find out what timezone the system is set to? PS: Iam not a full time programmer. Please excuse if this is a silly question to be asked. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i check system time zone from excel?
I'm not sure what you mean when you say "I have my calculations based on
CST", but I'm thinking you shouldn't do that. The problem is not in finding the time zone information on the computer your code is running on, but in knowing how that relates to your CST time. The problem comes about with Daylight Savings Time and the fact that the foreign computer cannot know whether your system is using DST and, if you are, when it started (that appears to vary around the globe). Also, not everyone using DST uses a one-hour offset, so deciding as to how to apply DST and by how much could prove problematic. In any event, here is the code to find out what the offset (bias) is and what the DST bias is.... '*************** Start Module Code*************** Public Declare Function GetTimeZoneInformation& Lib _ "kernel32" (lpTimeZoneInformation As _ TIME_ZONE_INFORMATION) Public 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 Public 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 '*************** End Module Code *************** '*************** Start of Code Where Needed *************** Dim TZ As TIME_ZONE_INFORMATION Dim TimeZoneBiasInMinutes As Long Dim DayLightSavingTimeBiasInMinutes As Long '..... '..... GetTimeZoneInformation TZ TimeZoneBiasInMinutes = TZ.Bias DayLightSavingTimeBiasInMinutes = TZ.DaylightBias '*************** End of Code Where Needed *************** Note that the TimeZoneBiasInMinutes is the number of minutes adjustment from Greenwich Mean Time. Normally, this is specified in hours, so if you want it that way, just divide by 60. -- Rick (MVP - Excel) "Yuvi" wrote in message ... I have a worksheet used by people across the globe and it involves time functions. I have my calculations based on CST. However, I cannot expect every one to have there system clock set to CST. Is there any easy method to find out what timezone the system is set to? PS: Iam not a full time programmer. Please excuse if this is a silly question to be asked. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i check system time zone from excel?
On Wed, 13 May 2009 09:21:02 -0700, Yuvi
wrote: I have a worksheet used by people across the globe and it involves time functions. I have my calculations based on CST. However, I cannot expect every one to have there system clock set to CST. Is there any easy method to find out what timezone the system is set to? PS: Iam not a full time programmer. Please excuse if this is a silly question to be asked. Not silly at all. Also not built into Excel or VBA. But see here for how to do it: http://www.cpearson.com/excel/TimeZo...lightTime.aspx --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i check system time zone from excel?
On Wed, 13 May 2009 12:59:10 -0400, "Rick Rothstein"
wrote: Note that the TimeZoneBiasInMinutes is the number of minutes adjustment from Greenwich Mean Time. Normally, this is specified in hours, so if you want it that way, just divide by 60. I know you wrote "usually" but a caveat is that all time zones are not exactly in increments of 60 minutes from UTC. Some are 30 and I believe there are 15 and 45 minute offsets also, in some locales. --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i check system time zone from excel?
http://www.timeanddate.com/worldclock/
"Ron Rosenfeld" wrote in message ... On Wed, 13 May 2009 12:59:10 -0400, "Rick Rothstein" wrote: Note that the TimeZoneBiasInMinutes is the number of minutes adjustment from Greenwich Mean Time. Normally, this is specified in hours, so if you want it that way, just divide by 60. I know you wrote "usually" but a caveat is that all time zones are not exactly in increments of 60 minutes from UTC. Some are 30 and I believe there are 15 and 45 minute offsets also, in some locales. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a time zone conversion function in Excel? | Excel Worksheet Functions | |||
convert time from one zone to other zone in excel | Excel Worksheet Functions | |||
Return System Time Zone | Excel Programming | |||
How do I display the time zone in Excel? | Excel Discussion (Misc queries) | |||
Determining current Time Zone In Excel | Excel Discussion (Misc queries) |