• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to display the system timezone in a excel cell ?

D

Dinesh Mishra

New Member
Hi Gurus

Please help me in getting the Macros to display the system timezone in a excel cell.
Appreciate you help.

thanks Dinesh
 
I want to display the output of system timezone in a excel cell using the vba/macros. I can get the system time by using the formula "=now()", but I am not able to to get any such formula to display the system timezone in the corresponding cell. please help me.
 
You could use a UDF like this:
Code: 
Function GetTimeZone() As String
    Dim oItem, colItems
    Application.Volatile True
    Set colItems = GetObject("winmgmts:\\.\root\cimv2").ExecQuery("Select * From Win32_TimeZone")
    For Each oItem In colItems
        GetTimeZone = oItem.Caption
    Next
End Function
 
@Hui I have gone through the cpearson and the stackoverflow articles but could not put the code working.... it gives me error at "
Private Declare Function GetTimeZoneInformation Lib "kernel32" _
(lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Private Declare Sub GetSystemTime Lib "kernel32" _
(lpSystemTime As SYSTEMTIME)
" ....

Hui, can you give me a code that actually works universally.
 
Check these two areas for Debaser's code:

The code must be in a code module
You use it as shown
upload_2016-5-26_21-12-14.png
 
Thanks Hui and Debaser.. it worked. I got the output, but can we get a proper format of timezone code like PST, EST, IST, CST etc...
 
On my side :

Caption = (UTC+01:00) Bruxelles, Copenhague, Madrid, Paris

StandardName = Paris, Madrid
​
 
From what the others are saying, I think you'll need a lookup table as Hui indicated earlier.
 
hi Debaser, Hui, Marc

In my excel sheet 1, I am displaying the output of times in various time zone. I have used the sample spreadsheet from http://www.tushar-mehta.com/excel/timezone.htm#The TimeZone VBA functions

to get the timezone conversion. However I feel there is some issue with the formulas used here.
1. For some timezones i am not getting the correct output.
2. If I set my system timezone to a different timezone, for eg. Pacific timezone, everything starts falling out of place.

I could not figure out what is causing the issue, I am attaching the spreadsheet, please help me figuring out where the problem lies and the fix to it.

regards
Dinesh
 

Attachments

  • SystemTZ.xlsm
    53.8 KB · Views: 21
Hi Dinesh ,

If you can explain your problem clearly , it would help.

1. Are you referring to the GetTimeZone function , or are you referring to other worksheet formulae which have been used in the workbook ?

2. If it is the GetTimeZone function , which data is the problem ?

3. If it is the worksheet formulae , which cells in which tab are the problem ?

Narayan
 
hi Narayan

1. if you see the "TimeZoneDB Sheet" sheet, though the formula used is same for column D, I see a discrepancy as below:
upload_2016-5-28_11-13-19.png
instead of -3.5 it shows as -33.
2. Please have a look on the "sheet1", I have highlighted in yellow, you could get the issue. PFA the spreadsheet.

regards
Dinesh
 

Attachments

  • SystemTZ.xlsm
    55.5 KB · Views: 16
Hi Dinesh ,

The formula itself is wrong ; the point is that the fault in the formula is being highlighted by the wrong display.

There is a division of the minutes by 60 in the first part of the formula , whereas it is missing in the second part of the formula.

As long as the times are integer hours , the problem never manifests itself , since the minutes are zero. When ever the minutes are present , the problem appears.

Narayan
 
You must log in or register to reply here.
Back
Top