Excel Macro: Convert Current System Time to ISO 8601 UTC Time Stamp

The Excel VBA Now function is a built-in function in Excel that return the current system date and time, but not contains milliseconds. We can't use Now to convert ISO 8601 UTC time stamp, so, we need a Macro to do this.

Convert Current System Time to ISO 8601 UTC Time Stamp

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

Public Function ISO8601TimeStampNoMS() As String
    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.
    ISO8601TimeStampNoMS = Application.WorksheetFunction.Text(utc, "yyyy-mm-ddThh:MM:ss.000Z")
    Set dt = Nothing
End Function

Examples

With Milliseconds

=ISO8601TimeStamp()

If you need to refresh the value in real time:

=ISO8601TimeStamp()&T(NOW())

No Milliseconds

=ISO8601TimeStampNoMS()

If you need to refresh the value in real time:

=ISO8601TimeStampNoMS()&T(NOW())

How to Use This Macro