La guía paso a paso o código que verás en este artículo es la resolución más sencilla y válida que encontramos a tus dudas o problema.
Solución:
Simplemente, puede usar el objeto COM para obtener información de la hora UTC.
Dim dt As Object, utc As Date
Set dt = CreateObject("WbemScripting.SWbemDateTime")
dt.SetVarDate Now
utc = dt.GetVarDate(False)
http://excel.tips.net/Pages/T002185_Automatically_Converting_to_GMT.html
Hay un macro en esa página con un método LocalTimeToUTC. Parece que haría el truco. También algunos ejemplos de fórmulas si quisieras ir por ese camino.
Editar – Otro enlace. http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx Esta página tiene varios métodos para fecha/hora. Elige tu veneno. Cualquiera de los dos debería hacer el truco, pero siento que el segundo es más bonito. 😉
De acuerdo, esta pregunta es antigua, pero acabo de pasar un tiempo reuniendo un código limpio basado en esto y quería publicarlo aquí en caso de que alguien que visite esta página pueda encontrarlo útil.
Cree un nuevo Módulo en el IDE de Excel VBA (opcionalmente dándole un nombre de UtcConverter o cualquiera que sea su preferencia en la hoja de propiedades) y pegue el código a continuación.
HTH
Option Explicit
' Use the PtrSafe attribute for x64 installations
Private Declare PtrSafe Function FileTimeToLocalFileTime Lib "Kernel32" (lpFileTime As FILETIME, ByRef lpLocalFileTime As FILETIME) As Long
Private Declare PtrSafe Function LocalFileTimeToFileTime Lib "Kernel32" (lpLocalFileTime As FILETIME, ByRef lpFileTime As FILETIME) As Long
Private Declare PtrSafe Function SystemTimeToFileTime Lib "Kernel32" (lpSystemTime As SYSTEMTIME, ByRef lpFileTime As FILETIME) As Long
Private Declare PtrSafe Function FileTimeToSystemTime Lib "Kernel32" (lpFileTime As FILETIME, ByRef lpSystemTime As SYSTEMTIME) As Long
Public Type FILETIME
LowDateTime As Long
HighDateTime As Long
End Type
Public Type SYSTEMTIME
Year As Integer
Month As Integer
DayOfWeek As Integer
Day As Integer
Hour As Integer
Minute As Integer
Second As Integer
Milliseconds As Integer
End Type
'===============================================================================
' Convert local time to UTC
'===============================================================================
Public Function UTCTIME(LocalTime As Date) As Date
Dim oLocalFileTime As FILETIME
Dim oUtcFileTime As FILETIME
Dim oSystemTime As SYSTEMTIME
' Convert to a SYSTEMTIME
oSystemTime = DateToSystemTime(LocalTime)
' 1. Convert to a FILETIME
' 2. Convert to UTC time
' 3. Convert to a SYSTEMTIME
Call SystemTimeToFileTime(oSystemTime, oLocalFileTime)
Call LocalFileTimeToFileTime(oLocalFileTime, oUtcFileTime)
Call FileTimeToSystemTime(oUtcFileTime, oSystemTime)
' Convert to a Date
UTCTIME = SystemTimeToDate(oSystemTime)
End Function
'===============================================================================
' Convert UTC to local time
'===============================================================================
Public Function LOCALTIME(UtcTime As Date) As Date
Dim oLocalFileTime As FILETIME
Dim oUtcFileTime As FILETIME
Dim oSystemTime As SYSTEMTIME
' Convert to a SYSTEMTIME.
oSystemTime = DateToSystemTime(UtcTime)
' 1. Convert to a FILETIME
' 2. Convert to local time
' 3. Convert to a SYSTEMTIME
Call SystemTimeToFileTime(oSystemTime, oUtcFileTime)
Call FileTimeToLocalFileTime(oUtcFileTime, oLocalFileTime)
Call FileTimeToSystemTime(oLocalFileTime, oSystemTime)
' Convert to a Date
LOCALTIME = SystemTimeToDate(oSystemTime)
End Function
'===============================================================================
' Convert a Date to a SYSTEMTIME
'===============================================================================
Private Function DateToSystemTime(Value As Date) As SYSTEMTIME
With DateToSystemTime
.Year = Year(Value)
.Month = Month(Value)
.Day = Day(Value)
.Hour = Hour(Value)
.Minute = Minute(Value)
.Second = Second(Value)
End With
End Function
'===============================================================================
' Convert a SYSTEMTIME to a Date
'===============================================================================
Private Function SystemTimeToDate(Value As SYSTEMTIME) As Date
With Value
SystemTimeToDate = _
DateSerial(.Year, .Month, .Day) + _
TimeSerial(.Hour, .Minute, .Second)
End With
End Function
Al final de la post puedes encontrar las críticas de otros programadores, tú aún tienes la habilidad dejar el tuyo si dominas el tema.