System time to GMT

[Caleb Runnels]

I'm trying to figure out a way to convert the system time to GMT for use in
a userform. Any suggestions would be greatly appreciated.

 

[Guest]

Sub HIJ()
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colTimeZones = objWMIService.ExecQuery("Select * From Win32_TimeZone")
For Each objTimeZone In colTimeZones
intTimeZoneBias = objTimeZone.Bias
intDayLightBias = objTimeZone.DaylightBias
Debug.Print intTimeZoneBias, intDayLightBias
Next

gives me
-300 -60

divide these by 60 and you get the number of hours you need to add to
GMT/UTC. to get local time. In my Case, Eastern Standard Time, I need to
subtract 5 hours and an additional hour for Daylight Savings Time.

To convert from local to GMT/UTC, I would subtract these values (net result
is to add 6 hours).

 

[Chip Pearson]

Caleb,

If you know your offset from GMT, use either

=A1+TIME(H,0,0)
or
=A1-TIME(H,0,0)

where A1 is the time and H is the absolute value of the offset in hours from
local time to GMT. Use the addition version of the formula if your local
time is earlier than GMT (you are in western longitude) or use the
subtraction formula if GMT is earlier than your local time (you are in
eastern longitude).

For an in depth discussion of GMT and Excel, including example formulas and
VBA code, see http://www.cpearson.com/excel/LocalAndGMTTimes.htm . Also see
http://www.cpearson.com/excel/FileTimes.htm for a discussion of the times
used by FILETIME, SYSTEMTIME and local times and conversion between them.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)