Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
RW RW is offline
external usenet poster
 
Posts: 49
Default How do I display the time zone in Excel?

If there is no funtion, does anyone know what file the time zone is stored in
for Windows?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 219
Default How do I display the time zone in Excel?

paste the User-Defined-Function TimeZoneInfo() in your workbook.
Call it with...
=TimeZoneInfo()
-Suggest you WordWrap for easy viewing with a column width of at least 35
-googled this response.


'from microsoft.public.vb.winapi
'Mar 20, 2001 4:53 pm
' contributed by Bob Butler ]
'/-----------------------------------------------------------/
' Time Zone API declarations
Private Const TIME_ZONE_ID_UNKNOWN = 0
Private Const TIME_ZONE_ID_STANDARD = 1
Private Const TIME_ZONE_ID_INVALID = &HFFFFFFFF
Private Const TIME_ZONE_ID_DAYLIGHT = 2

Private 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

Private Type TIME_ZONE_INFORMATION
bias As Long ' current offset to GMT
StandardName(1 To 64) As Byte ' unicode string
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName(1 To 64) As Byte
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type

Private Declare Function GetTimeZoneInformation Lib "kernel32" _
(lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

'/-----------------------------------------------------------/
Public Function TimeZoneInfo()
Dim x As Long ' scratch
Dim uTZI As TIME_ZONE_INFORMATION
Dim dtNow As Date ' current system time
Dim dtGMT As Date ' current GMT time
Dim lBias As Long ' current offset
Dim sName As String ' current tz name
Dim sDS As String ' Daylight Savings?
Dim sInfo As String ' scratch

Select Case GetTimeZoneInformation(uTZI)
' if not daylight assume standard
Case TIME_ZONE_ID_DAYLIGHT
sName = uTZI.DaylightName ' convert to string
lBias = uTZI.bias + uTZI.DaylightBias
sDS = "Daylight Savings Time is in effect"
Case Else
sName = uTZI.StandardName
lBias = uTZI.bias + uTZI.StandardBias
sDS = "Daylight Savings Time is not in effect"
End Select

' name terminates with null
x = InStr(sName, vbNullChar)

If x 0 Then
sName = Left$(sName, x - 1)
End If

dtNow = Now ' get time
dtGMT = DateAdd("n", lBias, dtNow) ' calculate GMT

' build string to display info
' sInfo = "Time Zone: " & sName & vbCrLf & _
"Local time: " & Format$(dtNow, "dd-mmm-yyyy hh:mm:ss") & _
vbCrLf & "GMT offset: " & CStr(lBias \ 60) & " hours, " & _
CStr(lBias Mod 60) & " minutes" & vbCrLf & _
sDS & vbCrLf & "GMT time: " & _
Format$(dtGMT, "dd-mmm-yyyy hh:mm:ss") & vbCrLf

sInfo = "Time Zone: " & sName & vbCrLf & _
"Local time: " & Format$(dtNow, "dd-mmm-yyyy hh:mm:ss") & _
vbCrLf & "GMT offset: " & CStr(lBias \ 60) & " hours, " & _
CStr(lBias Mod 60) & " minutes" & vbCrLf & _
sDS & vbCrLf & "GMT time: " & _
Format$(dtGMT, "dd-mmm-yyyy hh:mm:ss") & vbCrLf

TimeZoneInfo = sInfo

End Function
'/-----------------------------------------------------------/

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"RW" wrote:

If there is no funtion, does anyone know what file the time zone is stored in
for Windows?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
I don't want to display any controls in excel. areddy Excel Discussion (Misc queries) 0 October 31st 05 09:46 AM
What is the formula to display a negative time results? Robluc65 Excel Worksheet Functions 1 October 14th 05 11:15 PM
How do you display Greek and Russian languages in excel? Mark Hayden Excel Discussion (Misc queries) 1 August 1st 05 08:06 AM
Imported Date & Time format with calcs. managed in excel from imrp Todd F. Excel Worksheet Functions 0 July 8th 05 09:03 PM
Excel sheet for workout jogging time and graph Trevor New Users to Excel 1 March 7th 05 04:54 PM


All times are GMT +1. The time now is 09:35 AM.

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"