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.

Tim Williams · Answer 1 · 2019-12-05T21:03:43.840

56

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

edited Dec 05 '19 at 21:03

answered Sep 07 '12 at 22:17

Tim Williams

15

@xeo It is considered rude in SO to not accept answers that answer your question. So far you have asked several questions, got good answers, and accepted none of them. – chris neilsen Sep 08 '12 at 03:33
...and to create a UNIX timestamp : DateDiff("s", "1/1/1970", Now) – Fabien TheSolution Dec 10 '19 at 16:58
I'm confused. What about timezone support? – jonathanbell Feb 21 '22 at 18:29
@jonathanbell - date passed in is assumed to be UTC... – Tim Williams Feb 21 '22 at 18:41
@TimWilliams is that to say that all Date/Times in VBA are considered to be UTC? – jonathanbell Feb 22 '22 at 20:34
There's no timezone component in a VBA Date value - that's up to the programmer to manage... – Tim Williams Feb 22 '22 at 20:41

score 3 · Answer 2 · answered May 12 '20 at 08:12

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

2 Answers2