VBA to convert system time to different time zone

[kris21]

Hi Gurus,

How can I convert my system time to a different time zone . Say GMT? I use the below code to get the time of running the macro. I would like to display this time in GMT as the workbook is used by global audience.

   Sheet1.Range("C1").Value = DateTime.Now

Thanks in advance guys

 

[Kenneth Hobson]

See Chip Pearson's code. http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx

 

[kris21]

See Chip Pearson's code. http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx

Hi Kenneth,

Thank you. I have seen this post actually. I am not familiar using API's, so thought of looking for any other alternatives and ended up here.

 

[snb]

Sub M_snb()
    c00 = "HKLM\system\currentcontrolset\control\timezoneinformation\"
    With CreateObject("wscript.shell")
        MsgBox Now & "   (GMT " & IIf(.regread(c00 & "bias") < 0, "+", "-") & -.regread(c00 & "bias") \ 60 & ")"
    End With
End Sub

 

[Kenneth Hobson]

Don't be afraid of API calls or "long" code. Most don't know how all details about a car but they can drive it.

Chip's routine is extremely easy to use.
=ConvertLocalToGMT(A1, TRUE)

In any time conversion, you do need to know if daylight saving time was/is in effect. For me, it is.

This one works well too.
http://excel.tips.net/T002185_Automatically_Converting_to_GMT.html

 

[snb]

Some people are afraid to use 'normal' VBA:

Sub M_snb()
    MsgBox Now, , "Your time"

    MsgBox DateAdd("n", CreateObject("wscript.shell").regread("HKLM\system\currentcontrolset\control\timezoneinformation\bias"), Now), , "GMT time"
End Sub

 

[kris21]

Thank you so much for your time Kenneth and snb

Much Appreciated !!