Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Return System TimeZone in Abbreviation Format (i.e. CST)
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default Return System TimeZone in Abbreviation Format (i.e. CST)
Thanks. Now the output goes something like this: (GMT -6:00) Central
Time (US & Canada). Is there any code out there that can just make it
output the abbreviation, (i.e. CST for Central Time?).

Date: Sat, Dec 9 2006 7:46 am

Hi prahz,
If you want a simple function:
Function CurZone() As String
Const Key$ =
"HKLM\System\CurrentControlSet\Control\TimeZoneInf ormation\"
With CreateObject("WScript.Shell")
CurZone = .RegRead(Key & "StandardName") & " (" _
& .RegRead(Key & "ActiveTimeBias") / 60 & " hours for GMT)"
End With
End Function

If you want more details:
Function GMT_Info() As String
Dim CurZone As String
With CreateObject("WScript.Shell")
CurZone = .RegRead("HKLM\System\CurrentControlSet\" _
& "Control\TimeZoneInformation\StandardName")
End With
Const HKLM = &H80000002
Const Computer = "." ' Local machine
Const kPath = "Software\Microsoft\Windows NT\CurrentVersion\Time Zones"

Dim i%, Value$, SubKeys()
With GetObject("winmgmts:{impersonationLevel=impersonat e}!\\" _
& Computer & "\root\default:StdRegProv")
..EnumKey HKLM, kPath, SubKeys ' Enum all folders in kPath
For i = 0 To UBound(SubKeys)
..GetStringValue HKLM, kPath & "\" & SubKeys(i), "Std", Value
If Value = CurZone Then
..GetStringValue HKLM, kPath & "\" & SubKeys(i), "Display", Value
GMT_Info = Value
Exit For
End If
Next i
End With
End Function


Regards,
MP

I am creating an application that will be used by users in both the US
and India.

There is a cell in my worksheet that I want the Current Date, the
Current Time, and Time Zone of the user to output to.
-For example, in cell A3, I want the macro to output 12/8/06 4:05PM
(CST). I know how to get the current system date and time (using the
Now function), but how do I return the system time zone?

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 214
Default Return System TimeZone in Abbreviation Format (i.e. CST)
Hi prahz,
Without guarantee, but just to please to you:

Function GMT_Info(Optional Abbreviation As Boolean = True) As String
Dim CurZone As String
With CreateObject("WScript.Shell")
CurZone = .RegRead("HKLM\System\CurrentControlSet\" _
& "Control\TimeZoneInformation\StandardName")
End With
Const HKLM = &H80000002
Const Computer = "." ' Local machine
Const kPath = "Software\Microsoft\Windows NT\CurrentVersion\Time Zones"
Dim i%, Value$, SubKeys()
With GetObject("winmgmts:{impersonationLevel=impersonat e}!\\" _
& Computer & "\root\default:StdRegProv")
..EnumKey HKLM, kPath, SubKeys ' Enum all folders in kPath
For i = 0 To UBound(SubKeys)
..GetStringValue HKLM, kPath & "\" & SubKeys(i), "Std", Value
If Value = CurZone Then
If Abbreviation Then
GMT_Info = TZA(SubKeys(i))
Else
..GetStringValue HKLM, kPath & "\" & SubKeys(i), "Display", Value
GMT_Info = Value
End If
Exit For
End If
Next i
End With
End Function

Private Function TZA(ByVal TZI As String) As String
Select Case TZI
Case "Dateline Standard Time": TZA = "IDLE"
Case "Samoa Standard Time": TZA = "MIS"
Case "Hawaiian Standard Time": TZA = "HAW"
Case "Alaskan Standard Time": TZA = "ALA"
Case "Pacific Standard Time": TZA = "PST"
Case "Mountain Standard Time": TZA = "MST"
Case "Mexico Standard Time 2": TZA = "MST"
Case "US Mountain Standard Time": TZA = "MST"
Case "Canada Central Standard Time": TZA = "CST"
Case "Mexico Standard Time": TZA = "CST"
Case "Central Standard Time": TZA = "CST"
Case "Central America Standard Time": TZA = "CST"
Case "US Eastern Standard Time": TZA = "EST"
Case "Eastern Standard Time": TZA = "EST"
Case "SA Pacific Standard Time": TZA = "EST"
Case "Pacific SA Standard Time": TZA = "AST"
Case "Atlantic Standard Time": TZA = "AST"
Case "SA Western Standard Time": TZA = "AST"
Case "Newfoundland Standard Time": TZA = "NWF"
Case "Greenland Standard Time": TZA = "BBA"
Case "SA Eastern Standard Time": TZA = "BBA"
Case "E. South America Standard Time": TZA = "BBA"
Case "Mid-Atlantic Standard Time": TZA = "MAT"
Case "Azores Standard Time": TZA = "AZO"
Case "Cape Verde Standard Time": TZA = "AZO"
Case "GMT Standard Time": TZA = "GMT"
Case "Greenwich Standard Time": TZA = "GMT"
Case "W. Central Africa Standard Time": TZA = "AMS"
Case "W. Europe Standard Time": TZA = "AMS"
Case "Central Europe Standard Time": TZA = "AMS"
Case "Romance Standard Time": TZA = "AMS"
Case "Central European Standard Time": TZA = "AMS"
Case "GTB Standard Time": TZA = "AIM"
Case "E. Europe Standard Time": TZA = "BCP"
Case "South Africa Standard Time": TZA = "BCP"
Case "Israel Standard Time": TZA = "BCP"
Case "Egypt Standard Time": TZA = "BCP"
Case "FLE Standard Time": TZA = "HRI"
Case "Arabic Standard Time": TZA = "BKR"
Case "Arab Standard Time": TZA = "BKR"
Case "Russian Standard Time": TZA = "MSV"
Case "E. Africa Standard Time": TZA = "BKR"
Case "Iran Standard Time": TZA = "THE"
Case "Arabian Standard Time": TZA = "ABT"
Case "Caucasus Standard Time": TZA = "ABT"
Case "Afghanistan Standard Time": TZA = "KAB"
Case "Ekaterinburg Standard Time": TZA = "EIK"
Case "West Asia Standard Time": TZA = "EIK"
Case "India Standard Time": TZA = "BCD"
Case "Nepal Standard Time": TZA = "NPT"
Case "N. Central Asia Standard Time": TZA = "ADC"
Case "Central Asia Standard Time": TZA = "ADC"
Case "Sri Lanka Standard Time": TZA = "ADC"
Case "Myanmar Standard Time": TZA = "MMT"
Case "SE Asia Standard Time": TZA = "BHJ"
Case "North Asia Standard Time": TZA = "BHJ"
Case "North Asia East Standard Time": TZA = "SST"
Case "Singapore Standard Time": TZA = "SST"
Case "China Standard Time": TZA = "SST"
Case "W. Australia Standard Time": TZA = "SST"
Case "Taipei Standard Time": TZA = "SST"
Case "Tokyo Standard Time": TZA = "OST"
Case "Korea Standard Time": TZA = "SYA"
Case "Yakutsk Standard Time": TZA = "SYA"
Case "Cen. Australia Standard Time": TZA = "ADA"
Case "AUS Central Standard Time": TZA = "ADA"
Case "E. Australia Standard Time": TZA = "BGP"
Case "West Pacific Standard Time": TZA = "BGP"
Case "AUS Eastern Standard Time": TZA = "CMS"
Case "Tasmania Standard Time": TZA = "HVL"
Case "Vladivostok Standard Time": TZA = "HVL"
Case "Central Pacific Standard Time": TZA = "MSN"
Case "New Zealand Standard Time": TZA = "AWE"
Case "Fiji Standard Time": TZA = "FKM"
Case "Tonga Standard Time": TZA = "TOT"
Case Else: TZA = "???"
End Select
End Function

Regards,
MP

"prahz" a écrit dans le message de news:
...
Thanks. Now the output goes something like this: (GMT -6:00) Central
Time (US & Canada). Is there any code out there that can just make it
output the abbreviation, (i.e. CST for Central Time?).

Date: Sat, Dec 9 2006 7:46 am

Hi prahz,
If you want a simple function:
Function CurZone() As String
Const Key$ =
"HKLM\System\CurrentControlSet\Control\TimeZoneInf ormation\"
With CreateObject("WScript.Shell")
CurZone = .RegRead(Key & "StandardName") & " (" _
& .RegRead(Key & "ActiveTimeBias") / 60 & " hours for GMT)"
End With
End Function

If you want more details:
Function GMT_Info() As String
Dim CurZone As String
With CreateObject("WScript.Shell")
CurZone = .RegRead("HKLM\System\CurrentControlSet\" _
& "Control\TimeZoneInformation\StandardName")
End With
Const HKLM = &H80000002
Const Computer = "." ' Local machine
Const kPath = "Software\Microsoft\Windows NT\CurrentVersion\Time Zones"

Dim i%, Value$, SubKeys()
With GetObject("winmgmts:{impersonationLevel=impersonat e}!\\" _
& Computer & "\root\default:StdRegProv")
.EnumKey HKLM, kPath, SubKeys ' Enum all folders in kPath
For i = 0 To UBound(SubKeys)
.GetStringValue HKLM, kPath & "\" & SubKeys(i), "Std", Value
If Value = CurZone Then
.GetStringValue HKLM, kPath & "\" & SubKeys(i), "Display", Value
GMT_Info = Value
Exit For
End If
Next i
End With
End Function


Regards,
MP

I am creating an application that will be used by users in both the US
and India.

There is a cell in my worksheet that I want the Current Date, the
Current Time, and Time Zone of the user to output to.
-For example, in cell A3, I want the macro to output 12/8/06 4:05PM
(CST). I know how to get the current system date and time (using the
Now function), but how do I return the system time zone?



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default Return System TimeZone in Abbreviation Format (i.e. CST)
Michel, thanks alot, I hope you had that code on already on hand and
didn't do all of that from scratch!

Michel Pierron wrote:
Hi prahz,
Without guarantee, but just to please to you:

Function GMT_Info(Optional Abbreviation As Boolean = True) As String
Dim CurZone As String
With CreateObject("WScript.Shell")
CurZone = .RegRead("HKLM\System\CurrentControlSet\" _
& "Control\TimeZoneInformation\StandardName")
End With
Const HKLM = &H80000002
Const Computer = "." ' Local machine
Const kPath = "Software\Microsoft\Windows NT\CurrentVersion\Time Zones"
Dim i%, Value$, SubKeys()
With GetObject("winmgmts:{impersonationLevel=impersonat e}!\\" _
& Computer & "\root\default:StdRegProv")
.EnumKey HKLM, kPath, SubKeys ' Enum all folders in kPath
For i = 0 To UBound(SubKeys)
.GetStringValue HKLM, kPath & "\" & SubKeys(i), "Std", Value
If Value = CurZone Then
If Abbreviation Then
GMT_Info = TZA(SubKeys(i))
Else
.GetStringValue HKLM, kPath & "\" & SubKeys(i), "Display", Value
GMT_Info = Value
End If
Exit For
End If
Next i
End With
End Function

Private Function TZA(ByVal TZI As String) As String
Select Case TZI
Case "Dateline Standard Time": TZA = "IDLE"
Case "Samoa Standard Time": TZA = "MIS"
Case "Hawaiian Standard Time": TZA = "HAW"
Case "Alaskan Standard Time": TZA = "ALA"
Case "Pacific Standard Time": TZA = "PST"
Case "Mountain Standard Time": TZA = "MST"
Case "Mexico Standard Time 2": TZA = "MST"
Case "US Mountain Standard Time": TZA = "MST"
Case "Canada Central Standard Time": TZA = "CST"
Case "Mexico Standard Time": TZA = "CST"
Case "Central Standard Time": TZA = "CST"
Case "Central America Standard Time": TZA = "CST"
Case "US Eastern Standard Time": TZA = "EST"
Case "Eastern Standard Time": TZA = "EST"
Case "SA Pacific Standard Time": TZA = "EST"
Case "Pacific SA Standard Time": TZA = "AST"
Case "Atlantic Standard Time": TZA = "AST"
Case "SA Western Standard Time": TZA = "AST"
Case "Newfoundland Standard Time": TZA = "NWF"
Case "Greenland Standard Time": TZA = "BBA"
Case "SA Eastern Standard Time": TZA = "BBA"
Case "E. South America Standard Time": TZA = "BBA"
Case "Mid-Atlantic Standard Time": TZA = "MAT"
Case "Azores Standard Time": TZA = "AZO"
Case "Cape Verde Standard Time": TZA = "AZO"
Case "GMT Standard Time": TZA = "GMT"
Case "Greenwich Standard Time": TZA = "GMT"
Case "W. Central Africa Standard Time": TZA = "AMS"
Case "W. Europe Standard Time": TZA = "AMS"
Case "Central Europe Standard Time": TZA = "AMS"
Case "Romance Standard Time": TZA = "AMS"
Case "Central European Standard Time": TZA = "AMS"
Case "GTB Standard Time": TZA = "AIM"
Case "E. Europe Standard Time": TZA = "BCP"
Case "South Africa Standard Time": TZA = "BCP"
Case "Israel Standard Time": TZA = "BCP"
Case "Egypt Standard Time": TZA = "BCP"
Case "FLE Standard Time": TZA = "HRI"
Case "Arabic Standard Time": TZA = "BKR"
Case "Arab Standard Time": TZA = "BKR"
Case "Russian Standard Time": TZA = "MSV"
Case "E. Africa Standard Time": TZA = "BKR"
Case "Iran Standard Time": TZA = "THE"
Case "Arabian Standard Time": TZA = "ABT"
Case "Caucasus Standard Time": TZA = "ABT"
Case "Afghanistan Standard Time": TZA = "KAB"
Case "Ekaterinburg Standard Time": TZA = "EIK"
Case "West Asia Standard Time": TZA = "EIK"
Case "India Standard Time": TZA = "BCD"
Case "Nepal Standard Time": TZA = "NPT"
Case "N. Central Asia Standard Time": TZA = "ADC"
Case "Central Asia Standard Time": TZA = "ADC"
Case "Sri Lanka Standard Time": TZA = "ADC"
Case "Myanmar Standard Time": TZA = "MMT"
Case "SE Asia Standard Time": TZA = "BHJ"
Case "North Asia Standard Time": TZA = "BHJ"
Case "North Asia East Standard Time": TZA = "SST"
Case "Singapore Standard Time": TZA = "SST"
Case "China Standard Time": TZA = "SST"
Case "W. Australia Standard Time": TZA = "SST"
Case "Taipei Standard Time": TZA = "SST"
Case "Tokyo Standard Time": TZA = "OST"
Case "Korea Standard Time": TZA = "SYA"
Case "Yakutsk Standard Time": TZA = "SYA"
Case "Cen. Australia Standard Time": TZA = "ADA"
Case "AUS Central Standard Time": TZA = "ADA"
Case "E. Australia Standard Time": TZA = "BGP"
Case "West Pacific Standard Time": TZA = "BGP"
Case "AUS Eastern Standard Time": TZA = "CMS"
Case "Tasmania Standard Time": TZA = "HVL"
Case "Vladivostok Standard Time": TZA = "HVL"
Case "Central Pacific Standard Time": TZA = "MSN"
Case "New Zealand Standard Time": TZA = "AWE"
Case "Fiji Standard Time": TZA = "FKM"
Case "Tonga Standard Time": TZA = "TOT"
Case Else: TZA = "???"
End Select
End Function

Regards,
MP

"prahz" a écrit dans le message de news:
...
Thanks. Now the output goes something like this: (GMT -6:00) Central
Time (US & Canada). Is there any code out there that can just make it
output the abbreviation, (i.e. CST for Central Time?).

Date: Sat, Dec 9 2006 7:46 am

Hi prahz,
If you want a simple function:
Function CurZone() As String
Const Key$ =
"HKLM\System\CurrentControlSet\Control\TimeZoneInf ormation\"
With CreateObject("WScript.Shell")
CurZone = .RegRead(Key & "StandardName") & " (" _
& .RegRead(Key & "ActiveTimeBias") / 60 & " hours for GMT)"
End With
End Function

If you want more details:
Function GMT_Info() As String
Dim CurZone As String
With CreateObject("WScript.Shell")
CurZone = .RegRead("HKLM\System\CurrentControlSet\" _
& "Control\TimeZoneInformation\StandardName")
End With
Const HKLM = &H80000002
Const Computer = "." ' Local machine
Const kPath = "Software\Microsoft\Windows NT\CurrentVersion\Time Zones"

Dim i%, Value$, SubKeys()
With GetObject("winmgmts:{impersonationLevel=impersonat e}!\\" _
& Computer & "\root\default:StdRegProv")
.EnumKey HKLM, kPath, SubKeys ' Enum all folders in kPath
For i = 0 To UBound(SubKeys)
.GetStringValue HKLM, kPath & "\" & SubKeys(i), "Std", Value
If Value = CurZone Then
.GetStringValue HKLM, kPath & "\" & SubKeys(i), "Display", Value
GMT_Info = Value
Exit For
End If
Next i
End With
End Function


Regards,
MP

I am creating an application that will be used by users in both the US
and India.

There is a cell in my worksheet that I want the Current Date, the
Current Time, and Time Zone of the user to output to.
-For example, in cell A3, I want the macro to output 12/8/06 4:05PM
(CST). I know how to get the current system date and time (using the
Now function), but how do I return the system time zone?


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
excel to return system time when I press combination of keys Nagendra Excel Worksheet Functions 2 September 25th 07 10:38 PM
Timezone converter Chris Excel Worksheet Functions 1 June 13th 07 04:21 PM
Return System Time Zone prahz Excel Programming 3 December 11th 06 10:03 PM
How do I convert GMT date/time to different timezone Hooves57 Excel Worksheet Functions 2 August 30th 06 05:36 AM
How to get Timezone in VBA (for Excel)? Devangi Gandhi Excel Programming 2 July 31st 03 08:09 PM


All times are GMT +1. The time now is 06:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement