Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return System Time Zone
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return System Time Zone
Excel uses the local time and terefore you need to use system calls to get
the information. Chip Pearson (http://www.cpearson.com) has a good description with examples of using time zones at http://www.cpearson.com/excel/timezone.htm -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "prahz" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return System Time Zone
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 "prahz" a écrit dans le message de news: ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return System Time Zone
Michel,
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?). Thanks. Michel Pierron wrote: 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 "prahz" a écrit dans le message de news: ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel to return system time when I press combination of keys | Excel Worksheet Functions | |||
convert time from one zone to other zone in excel | Excel Worksheet Functions | |||
Time Zone.. | Excel Discussion (Misc queries) | |||
Time zone using VBA | Excel Programming | |||
Time zone using VBA | Excel Programming |