Is there a way to retrieve the current date and time in UTC format for Excel VBA macros?You can retrieve the current time in the local time zone by calling Now(). Is there a common way to convert this to UTC?http://excel.tips.net/Pages/T002185_Automatically_Converting_to_GMT.html There is a macro on that page that has the LocalTimeToUTC method. It looks like it’s going to trick. And if you want that route, some official examples. Edit – Other links. http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx This page has several ways. Choose poison. Either one has to trick, but I think the second one is prettier. 😉
If daylight saving time should also be considered, the following code may be useful.
인기글
![[MariaDB] MariaDB/MySQL queries I learned when I came here with Oracle](https://t1.daumcdn.net/cfile/tistory/2268AF40586BA94A11 "[MariaDB] MariaDB/MySQL queries I learned when I came here with Oracle")
If daylight saving time should also be considered, the following code may be useful.
If daylight saving time should also be considered, the following code may be useful.
オプション Explicit”””””””””””Windows API Structures””””””””””””””””””””””””””””””””””””’ ”””””””””””””””””””” IntegerEnd Type Private Type TIME_ZONE_INFORMATION Bias Long StandardName(0 ~ 31) As Integer StandardDate As Long DaylightName(0 ~ 31) As Integer DaylightDate As Long End Type ”” ”””””””””””’ ”””””””””’ WINDOWS API ”””””””””””””””””’プライベート宣言関数 GetTimeZoneInformation Lib “kernel32” _(lpTimeZoneInformation As TIME_ZONE_INFORMATION) LongPrivate宣言関数 TzSpecificLocalTime としてインポートするToSystemTimeLib “kernel32” _(lpTimeZoneInformationAsTIME_ZONE_INFORMATION, lpLocalTimeAsSYSTEM_TIME, lpUniversalTimeAsSYSTEM_TIME) 整数としてUniversalTime (localTimeAsDate) AsDate Dim timeZoneInfo AsTIME_ZONE_INFORMATION GetTimeZoneInformation timeZoneInfo Dim localSystemTime AsSYSTEM_TIME With localSystemTimeYear = Year（現地時間）。wMonth = Month（現地時間）。wDay = Day (localTime) DimutcSystemTime を SYSTEM_TIME として終了（TzSpecificLocalTime の場合）ToSystemTime(timeZoneInfo, localSystemTime, utcSystemTime) => 0 ThenToUniversalTime = SystemTimeToVBTime(utcSystemTime) Else error。Raise 1, “WINAPI”, “Windows API call failed” End IfEnd FunctionPrivate Function SystemTimeToVBTime(system)Time As SYSTEM_TIME) As Date With systemTime SystemTimeToVBTime = DateSerial(.)。wYear、.wMonth、.wDay) + _ TimeSerial(。wHour、.wMinute、.wSecond）End 関数で終了する
If you only need the current time, you can perform this task using GetSystemTime, which has fewer Win32 calls. Provides a millisecond-precision time structure that allows you to specify the desired format.
Private Declarate PtrSafe SubGetSystemTime Lib “Kernel32” (ByRefSystemTimeAsSYSTEMIMEasSYSTEMIMEwYear As Integer wDayOfWeek As Integer wHour As Integer wMinute As Integer wSeconds As Integer End Type) 整数 wMillisecondseconds 整数weconds（wDay As（wDay As（w曜日
the use.
DimnowUtcAsSYSTEMTIMECallGetSystemTime(nowUtc) ‘ nowUtc is populated with the current UTC time. Convert to format or date, if necessary.
DimnowUtcAsSYSTEMTIMECallGetSystemTime(nowUtc) ‘ nowUtc is populated with the current UTC time. Convert to format or date, if necessary.
DimnowUtcAsSYSTEMTIMECallGetSystemTime(nowUtc) ‘ nowUtc is populated with the current UTC time. Convert to format or date, if necessary.Most My Access projects work with Access tables attached to the MS SQL Server table. This is a DAO project, and there was also a problem reacquiring SQL sproc using GETUTCDATE(). But next was my solution.– Create a SQL table with the UTCDATE CREATE TABLE [dbo] calculation field.[tblUTcdate]([ID] [int] NULL, [UTCDate] AS (getutcdate()) ON [PRIMARY]GOCreate an access table dbo_tblUTCDate that is connected to the SQL table tblUTCDate via ODBC.Create an Access query to select from the Access table. I called that qryUTCDate.Select dbo_tblUTC date.UTC date from dbo_tblUTC dateFrom VBA:Specify db as DAO.database, rsAS RecordsetSets = db.OpenRecordset(“qryUTCDate”)Debug.Print CStr(rs！UTCDATE)rs.CloseSets = Close Nothingdb.CloseSetdb = NoneSpecify db as DAO.database, rsAS RecordsetSets = db.OpenRecordset(“qryUTCDate”)Debug.Print CStr(rs！UTCDATE)rs.CloseSets = Close Nothingdb.CloseSetdb = NoneSpecify db as DAO.database, rsAS RecordsetSets = db.OpenRecordset(“qryUTCDate”)Debug.Print CStr(rs！UTCDATE)rs.CloseSets = Close Nothingdb.CloseSetdb = NoneSimply put, you can use COM objects to obtain UTC time information.Dimdt As Object, utc As DateSetdt = CreateObject(“WbemScripting”).SWbemDateTime”)dt.Set VarDateNowutc = dt.GetVarDate(False)Dimdt As Object, utc As DateSetdt = CreateObject(“WbemScripting”).SWbemDateTime”)dt.Set VarDateNowutc = dt.GetVarDate(False)Dimdt As Object, utc As DateSetdt = CreateObject(“WbemScripting”).SWbemDateTime”)dt.Set VarDateNowutc = dt.GetVarDate(False)Dimdt As Object, utc As DateSetdt = CreateObject(“WbemScripting”).SWbemDateTime”)dt.Set VarDateNowutc = dt.GetVarDate(False)Sigh, the question is about Excel VBA, not Excel VBA for Windows.In short, voting for any answer will not work on Mac or Linux (e.g., Office, at least Office 2000 will also run on Linux).Here’s my reply. You can expect to get zero votes, but the truth is not a beauty contest.For Windows, see a different answer (an answer that tells you how to automatically assume Windows and generate problems for the programmer).For Macs, see https://macscripter.net/viewtopic.php?id=41117 For Linux, see how to find GMT time on Unix. Have a good time with VBA. Sorry it’s too dry, but SO is already scary because there are so many approximations going on.I admit that this question is old, but I wanted to spend some time creating some clean code based on it and post it here in case anyone visiting this page finds it useful.Create a new module in Excel VBA IDE (selectively name it UtcConverter or as you like in the properties sheet) and paste the following code.HTHI admit that this question is old, but I wanted to spend some time creating some clean code based on it and post it here in case anyone visiting this page finds it useful.Create a new module in Excel VBA IDE (selectively name it UtcConverter or as you like in the properties sheet) and paste the following code.HTHI admit that this question is old, but I wanted to spend some time creating some clean code based on it and post it here in case anyone visiting this page finds it useful.Create a new module in Excel VBA IDE (selectively name it UtcConverter or as you like in the properties sheet) and paste the following code.HTHオプション Explicit ‘x64 インストールに PtrSafe アトリビュートを使用する Private Declarate PtrSafe Function FileTimeToLocalFileTime Lib “Kernel32” (lpFileTime As FILETIME, ByReflpLocalFileTime As FILETIME)As LongPrivate PtrSafeFileTime Lib “Kernel32” (lpFileTime As FileTime As FileTime, FileTime、 LongPrivate は PtrSafe 関数 SystemTimeToFileTime Lib “Kernel32” (lpSystemTime As SYSTEMTIME, ByReflpFileTime As FILETIME) を LongPrivate は PtrSafe 関数 FileTime として宣言しますSystemTime Lib “Kernel32” (lpFileTime As FILETIME、 ByRefSystemTimeAsSYSTEMTIME）LongPublicType FILETIME LowDateTimeAsLongHighDateTimeAsLongEndTypeパブリックタイプSYSTEMTIMEYYearAs整数日（週の整数日）を整数時間として整数分秒を整数として整数ミリ秒を指定する Type ‘===============================================================================’ ローカル時刻を UTC =============================================================================== Public Function UTCTIME (LocalTime As Date) As DateDimoLocalFileTime As FILETIME DimoUTCFileTime As FILETIME DimoSystemTime As SYSTEMTIME ‘ への変換 aSYSTEMTIME oSystemTime = DateToSystemTime (LocalTime) ‘ 1. ファイルタイムに変換 ‘ 2. UTCタイムに変換 ‘ 3. SYSTEMTIME Call SystemTimeToFileTime(oSystemTime, oLocalFileTime) CallLocalFileTimeToFileTime(oLocalFileTime, oUTCFileTime) コールファイルタイムToSystemTime(oUtcFileTime, oSystemTime) ‘ 日付への変換 UTCTIME = SystemTimeToDate(oSystemTime)End 関数 ‘===============================================================================’ UTC を local time に変換’ =============================================================================== Public 関数 LOCALTIME(UtcTime As Date) As DateDimoLocalFileTime As FILETIME DimoUtcFileTime As FILETIME As SYSTEMTIME ‘ SYSTEM TIME に変換します。 oSystemTime = DateToSystemTime(UtcTime) ‘ 1. FILETIME に変換 ‘ 2. ローカル時間に変換 ‘ 3. SYSTEMTIME CallSystemTimeToFileTime(oSystemTime, oUtcFileTime) CallFileTime(oUtcFileTime, oLocalFileTime) CallFileTimeToSystemTime(oLocalFileTime, oSystemTime) ‘ 日付に変換 LOCALTIME = SystemTimeToDate(oSystemTime)終了関数 ‘===============================================================================’ 日付をシステム時刻に変換’===============================================================================プライベート関数 DateToSystemTime(ValueAsDate) AsSYSTEMTIME With DateToSystemTime 。Year = Year（値）。Month = Month（値）。日 = 日（値）。Hour = Hour（値）。minute = Minute（値）。Second = Second(Value) End With End 関数 ‘===============================================================================’ SYSTEMTIME を日付に変換する’ =============================================================================== Private Function SystemTimeToDate(Value As SYSTEMTIME) As Date With Value SystemTimeToDate = _ DateSerial)。年、.月、.日) + _ TimeSerial(。時、分、分。2つ目）End With End 関数