CODEWITHSUNDEEP
vbaexceldatetime
Dmitrij Holkin
Dmitrij Holkin

Reputation: 2055

Get Unix time milliseconds

In Java to get system time in milliseconds I use:

 new date().gettime()

It is possible to get the same result in milliseconds using Excel VBA?

Upvotes: 10

Views: 10341

Answers (6)

bouvierr
bouvierr

Reputation: 3781

SUMMARY: For best results, use GetSystemTime.

The Excel worksheet function Now() has relatively good precision, roughly down to 10 ms. But to call it you have to use a worksheet formula.

To correctly get the milliseconds value, you should avoid the VBA Now() function. Its precision is roughly 1 second.

The VBA Timer() function returns a single with a precision of roughly 5 milliseconds. But you have to use Now() to get the date part. This might cause a slight problem if Now() is called before midnight and Timer() is called after midnight (this is probably a rare situation and not an issue for most people).

The Windows API function GetSystemTime has true millisecond precision. You can use the values in the SYSTEMTIME structure to create an Excel double that has the correct millisecond precision. GetSystemTime returns the UTC time so if you want the date in POSIX format, you can subtract the UNIX epoch (1 January 1970 UTC), which is 25569 in Excel date format (disregarding leap seconds).

The code below compares the precision of each method:

Option Explicit

Private Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type

' Retrieves the current system date and time in Coordinated Universal Time (UTC) format.
' To retrieve the current system date and time in local time, use the GetLocalTime function.
Private Declare PtrSafe Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)

Function Now_System() As Double
    Dim st As SYSTEMTIME
    GetSystemTime st
    Now_System = DateSerial(st.wYear, st.wMonth, st.wDay) + _
        TimeSerial(st.wHour, st.wMinute, st.wSecond) + _
        st.wMilliseconds / 86400000#
End Function

Function Now_Timer() As Double
    Now_Timer = CDbl(Int(Now)) + CDbl(Timer() / 86400#)
End Function


Sub CompareCurrentTimeFunctions()
    ' Compare precision of different methods to get current time.
    Me.Range("A1:D1000").NumberFormat = "yyyy/mm/dd h:mm:ss.000"

    Dim d As Double
    Dim i As Long
    For i = 2 To 1000
        ' 1) Excel NOW() formula returns same value until delay of ~10 milliseconds. (local time)
        Me.Cells(1, 1).Formula = "=Now()"
        d = Me.Cells(1, 1)
        Me.Cells(i, 1) = d

        ' 2) VBA Now() returns same value until delay of ~1 second. (local time)
        d = Now
        Me.Cells(i, 2) = d

        ' 3) VBA Timer returns same value until delay of ~5 milliseconds. (local time)
        Me.Cells(i, 3) = Now_Timer
        
        ' 4) System time is precise down to 1 millisecond. (UTC)
        Me.Cells(i, 4) = Now_System
    Next i
End Sub

Upvotes: 5

Geovani Ceos
Geovani Ceos

Reputation: 11

Seems to work well:

Format(Now, "h:mm:ss") & Right(Format(Timer, "0.000"), 4)

Upvotes: 1

David Robson
David Robson

Reputation: 312

Here is a short extension on the answer by @bouvierr as I needed the equivalent of the java.lang.System.currentTimeMillis() method in VBA:

Private Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type

Private Declare Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)

Function CurrentTimeMillis() As Double
    ' Returns the milliseconds from 1970/01/01 00:00:00.0 to system UTC
    Dim st As SYSTEMTIME
    GetSystemTime st
    Dim t_Start, t_Now
    t_Start = DateSerial(1970, 1, 1) ' Starting time for Linux
    t_Now = DateSerial(st.wYear, st.wMonth, st.wDay) + _
        TimeSerial(st.wHour, st.wMinute, st.wSecond)
    CurrentTimeMillis = DateDiff("s", t_Start, t_Now) * 1000 + st.wMilliseconds
End Function

Upvotes: 4

Dmitrij Holkin
Dmitrij Holkin

Reputation: 2055

I found only one possible variant

Private Type SYSTEMTIME
        wYear As Integer
        wMonth As Integer
        wDayOfWeek As Integer
        wDay As Integer
        wHour As Integer
        wMinute As Integer
        wSecond As Integer
        wMilliseconds As Integer
End Type

Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
Sub test()
Dim sSysTime As SYSTEMTIME

GetLocalTime sSysTime

MsgBox = ((Now - 25569) * 86400000) - 3600000 + sSysTime.wMilliseconds
End Sub

Upvotes: 1

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

This produces a timestamp in format yyyy mm dd hh:mm:ss.fff where fff are the milliseconds. 

Dim dateToday As Date
Dim datetimeNow As Date
Dim secondsElapsedSinceMidnight As Double
Dim h As Long
Dim m As Long
Dim s As Long

dateToday = Now
secondsElapsedSinceMidnight = Timer

h = Int(secondsElapsedSinceMidnight / 3600)
m = Int(secondsElapsedSinceMidnight / 60) - h * 60
s = Int(secondsElapsedSinceMidnight) - m * 60 - h * 3600

datetimeNow = DateSerial(Year(dateToday), Month(dateToday), Day(dateToday)) _
    + TimeSerial(h, m, s)

Debug.Print Format(datetimeNow, "yyyy mm dd hh:nn:ss.") _
    & Format((secondsElapsedSinceMidnight _
      - Int(secondsElapsedSinceMidnight)) * 1000, "000")

As I submit this answer, the output is:

2015 04 21 16:24:22.852

Upvotes: 1

pnuts
pnuts

Reputation: 59442

Different interpretation, based on Excel posix time and with an hour adjustment for summer time: 

Sub Pose()
    ut = ((Now - 25569) * 86400000) - 3600000
End Sub

If not sufficiently precise, http://vbadud.blogspot.co.uk/2008/10/excel-vba-timestamp-milliseconds-using.html may be of interest.

Upvotes: 4

Related Questions