How to get Unix Timestamp in milliseconds VBA?

[mintz]

I want to get the current UTC time in milliseconds since 00:00:00 UTC on 1 January 1970 (Unix Epoch TimeStamp)

I tried this:

DateDiff("s", "01/01/1970 00:00:00", Now())

The result I am getting is 1477952802 (only seconds)

How do I get the result in milliseconds?

 

[shg]

Multiply by 1000.

	A​	B​
1​	01 Jan 1970 00:00​	A1: Input
2​	31 Oct 2016 17:50​	A2: =NOW()
3​	1,477,936,210,610​	A3: =(A2 - A1) * 86400000

 

[Tetra201]

Try this (Note: Windows only):

DateDiff("d", "01/01/1970", Now()) * 24 * 60 * 60 + Timer

 

[joeu2004]

How to get Unix Timestamp in milliseconds VBA?
I want to get the current UTC time in milliseconds since 00:00:00 UTC on 1 January 1970 (Unix Epoch TimeStamp)

Sub doit()
Dim utc As Double, d As Double, t As Double
Do
    d = Date
    t = Timer
Loop Until d = Date  ' loops only if midnight occurs between statements
d = d - DateSerial(1970, 1, 1)
utc = Int(d * 86400000 + t * 1000)
MsgBox Format(d * 86400, "#,##0.000000") & " sec" & _
    vbNewLine & Format(t, "#,##0.000000") & " sec" & _
    vbNewLine & Format(utc, "#,##0") & " msec"
End Sub

It is essential that utc is type Double, not type Long. Type Long bits is good only for 24d 20h 31m 23.647s since 1/1/1970.

I prefer to use type Double for d and t, as well. Some people use type Date. But I don't trust it, since VBA interprets type Date values in special ways in some contexts.

It is essential to use Timer, not VBA Now or Excel Now. VBA Now = Date + Time, and VBA Time is truncated to the current second. Excel Now is truncated to the current 1/100 second.

On Windows computers, VBA Timer is precise to at least the microsecond (actually 1/128 second); more depending on the time of day. However, the resolution of the system clock is 15.625 milliseconds (1/64 second) by default. (That might be changed by applications, though.)

VBA documentation says Timer is precise only to the second on the Mac. I cannot verify that because I am not a Mac user. And that raises a question about the precision of Excel Now on the Mac.

 

[John_w]

How do I get the result in milliseconds?

Split((DateDiff("s", "01/01/1970", Date) + Timer) * 1000, ".")(0)

 

[mintz]

All solutions worked perfectly thank you so much!

 

[wangweiggsn]

Try this (Note: Windows only):

DateDiff("d", "01/01/1970", Now()) * 24 * 60 * 60 + Timer

DateDiff("d", "01/01/1970", Date) * 24 * 60 * 60 + Timer - 10.5 * 3600
Because my time zone is +10.5