Excel VBA: Convert a date string to a Unix timestamp

Question

Excel VBA: How to convert date string
"2012-08-20" to timestamp: 1345438800
I need to store 1345438800 in cell as long data type value.

Answer 1

Date to timestamp:

Public Function toUnix(dt) As Long
    toUnix = DateDiff("s", "1/1/1970", dt)
End Function

Timestamp to date:

Public Function fromUnix(ts) As Date
    fromUnix = DateAdd("s", ts, "1/1/1970")
End Function

Answer 2

To ensure an accurate complete round trip, add a timestamp to the DateDiff and DateAdd functions:

Date to timestamp:

Public Function toUnix(dt) As Long
    toUnix = DateDiff("s", "1/1/1970 00:00:00", dt)
End Function

Timestamp to date:

Public Function fromUnix(ts) As Date
    fromUnix = DateAdd("s", ts, "1/1/1970 00:00:00")
End Function

Answer 3

The current answers to this question won't work for dates beyond 2038-01-18 due to the Year 2038 problem.

To avoid overflow error when the date is larger than 2038-01-18, you can use a LongLong argument which will give you a 64-bit long to work with.

Timestamp from a date:

Public Function UnixFromDate(ByVal dt As Date) As LongLong
    UnixFromDate= DateDiff("s", "1/1/1970 00:00:00", dt)
End Function

Date from a timestamp:
(Note that DateAdd takes a double, so we can't use it and have to implement it ourselves.)

Function DateFromUnix(ByVal unixTime As LongLong) As Date
    Dim epochTime As Date
    epochTime = #1/1/1970#
    DateFromUnix = epochTime + (unixTime \ 86400) + ((unixTime Mod 86400) / 86400#)
End Function

This assumed that you are using a 64-bit version of Office.

If you need to support older versions of Office, you could still use the Decimal data type

Note that at this time the Decimal data type can only be used within a Variant. You cannot declare a variable to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function. See data type summary.

Here's what I've tried that worked on my 64-bit version of Office (inspired by this answer):

Public Function UnixFromDate32bit(ByVal dt As Date) As Variant
    UnixFromDate32bit = DateDiff("s", "1/1/1970 00:00:00", dt)
End Function

Function DateFromUnix32bit(ByVal unixTime As Variant) As Date
    Dim epochTime As Date
    epochTime = #1/1/1970#
    Dim unixDecimal As Variant
    unixDecimal = CDec(unixTime)
    Dim days As Long
    days = Fix(unixDecimal / 86400)
    unixDecimal = CDec(unixDecimal - (days * CDec(86400)))
    DateFromUnix32bit = epochTime + days + (unixDecimal / 86400)
End Function