Display GMT Time Automatically

[roudo99]

I am using an excel sheet that displays local time (=NOW) in cell B3
The same sheet displays GMT time (=NOW()-10/24 )in cell C3
This works perfectly for my time zone(GMT+10) but will not work in other time zones.
Is it possible to display real GMT data in cell C3 so users in different time zones can use this worksheet?
I currently need to press F9 to update both times, and would prefer that they were automatically updated from computer clock - is this possible?

 

[nightcrawler23]

How will excel know, in which timezone you are?

 

[RSXchin]

a custom function perhaps? one that uses environ.

 

[roudo99]

nightcrawler23
Time is taken from the computer being used, and computers in every time zone are set to thier respective local time (GMT+XX)

 

[nightcrawler23]

nightcrawler23
Time is taken from the computer being used, and computers in every time zone are set to thier respective local time (GMT+XX)

That is what i wanna say. Excel can take the time from the computer. Can it access the timezone also.

 

[roudo99]

I'm sure that excel can access the correct time, but I do not know a function that will achieve this?

 

[xenou]

Not sure (as I haven't looked closely) but Chip Pearson seems once again to have done all the work for us:

http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx

Note that he provides a download link at the bottom of the page for all the code -- give his API's a try and see if that does the trick for you.

Edit:
also came across this link:
http://excel.tips.net/T002185_Automatically_Converting_to_GMT.html

(thanks to a certain brad.huffman at StackOverflow:
http://stackoverflow.com/questions/...rrent-datetime-in-utc-from-an-excel-vba-macro)

 

[roudo99]

Thanks xenou, that appears to be just what I need.
Unfortunately when I tried to run it there was an error:
'Thew error tracer encountered existing arrows or a circular reference'
I am not sure what this means, and am unable to use the code supplied.
Could you please explain where I am going wrong?

 

[Ruddles]

Not sure (as I haven't looked closely) but Chip Pearson seems once again to have done all the work for us:

http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx

I've used this and the code dropped straight into a workbook and worked first time.

 

[Norie]

It doesn't look like a code error but a worksheet formula/function error.

Where/how did you get the error?