How to get seconds since epoch (1/1/1970) in VBA?

Question

How can I get seconds since epoch (1/1/1970) in VBA?

score 19 · Accepted Answer · answered Feb 13 '10 at 23:05

19

How about:

datediff("s",#1970/1/1#,now())

answered Feb 13 '10 at 23:05

Fionnuala

90,370
7
114
152

Yeah, I changed it to this `datediff("s",#1970/1/1#,dateadd("h",5,now()))`... not sure what I'll do when DST rolls around though. – Aug 15 '16 at 19:39
So there is no way to handle the conversion to UTC automatically? – R01k Feb 14 '18 at 23:58

score 18 · Answer 2 · answered Feb 18 '10 at 14:35

This should run faster than the DateDiff solution:

Private Function Long2Date(lngDate As Long) As Date
    Long2Date = lngDate / 86400# + #1/1/1970#
End Function

Private Function Date2Long(dtmDate As Date) As Long
    Date2Long = (dtmDate - #1/1/1970#) * 86400
End Function

score 1 · Answer 3 · edited Mar 27 '15 at 11:09

1

Here's a solution: http://vbcity.com/forums/t/5084.aspx

Function UnixTime() As Variant
    'The first parameter determines how the 
    ' difference will be measured in i.e. "S" for seconds
    UnixTime = DateDiff("S", "1/1/1970", Now())
End Function

edited Mar 27 '15 at 11:09

Gunther Struyf

11,158
2
34
58

answered Feb 13 '10 at 22:36

score 1 · Answer 4 · answered May 07 '21 at 19:17

Here's a solution that returns at UTC because the DateDiff one returns a value based on your time zone:

Private Function UnixEpoch() As Long

    Set ScriptEngine = CreateObject("MSScriptControl.ScriptControl")
    ScriptEngine.Language = "JScript"
    
    ScriptEngine.AddCode "function unixTime() { var d = new Date(); var s = Math.round(d.getTime() / 1000); return s; } "
    
    UnixEpoch = ScriptEngine.Run("unixTime")

End Function

Running it on JScript takes care of the time zone workarounds that would've been necessary in VBA.

score 0 · Answer 5 · answered Apr 10 '18 at 17:45

I tryed modify it for my timezone and take into account DST. Timezones can have diferent settings when it changes.

Function Epoch2Date(lngDate As Long) As Date
'transfer to date
    Epoch2Date = lngDate / 86400# + #1/1/1970#
'check if it is summer time
    If IsDST(Epoch2Date) = False Then
'here you can use diferent values depend on time zone
    Epoch2Date = Epoch2Date - 0.041666667
    Else
    Epoch2Date = Epoch2Date - 0.0833333333333333
    End If
End Function

Public Function IsDST(ByVal d0 As Date) As Boolean
   IsDST = d0 >= NextSun("24.3." & Year(d0) & " 01:59:59") And d0 < NextSun("24.10." & Year(d0) & " 01:59:59")
End Function

Private Function NextSun(d1 As Date) As Date
        'if 24.3 or 24.10 is sunday returns 31.3 or 31.10
        If Weekday(d1, vbMonday) = 7 Then
            NextSun = d1 + 7
        Else
        'if not return nearest sunday
            NextSun = d1 + 7 - Weekday(d1, vbMonday)
        End If
    End Function

score 0 · Answer 6 · edited Sep 19 '18 at 12:58

0

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

edited Sep 19 '18 at 12:58

Papershine

4,995
2
24
48

answered Sep 19 '18 at 12:57

JOAN

1

UnixTimeStamp = DateDiff("s", "01/01/1970 00:00:00", Now()) & Format(Now(), "ms") would give you UnixTimeStamp as 1537362312758 – JOAN Sep 19 '18 at 12:58

How to get seconds since epoch (1/1/1970) in VBA?

6 Answers6