EXCEL VBA Convert Current System Time to ISO 8601 UTC Time Stamp (with Milliseconds)

The Excel VBA NOW function is a built-in function in Excel that return the current system date and time, but not contains milliseconds. So, we can't use NOW to convert ISO 8601 UTC time stamp.

With Milliseconds

'Private Declare Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
Private Declare PtrSafe Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)   'windows 64bit
Private 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

Public Function ISO8601TimeStamp() As String
    Dim t As SYSTEMTIME, currentime As String
    GetSystemTime t
    CurrentTime = t.Year & "/" & t.Month & "/" & t.Day & " " & t.Hour & ":" & t.Minute & ":" & t.Second & "." & t.Milliseconds
    ISO8601TimeStamp = Application.WorksheetFunction.Text(CurrentTime, "yyyy-mm-ddThh:MM:ss.000Z")
End Function

No Milliseconds

If you don't need milliseconds(milliseconds will be display 000), try this code:

Sub ISO8601TimeStampNoMS()
   
    Dim dt As Object, utc As Date, timestamp As String
    Set dt = CreateObject("WbemScripting.SWbemDateTime")
    dt.SetVarDate Now
    utc = dt.GetVarDate(False)  'False: UTC time, True: local time.
    timestamp = Application.WorksheetFunction.Text(utc, "yyyy-mm-ddThh:MM:ss.000Z")
    Set dt = Nothing

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *