Converting TO unix datestamp in VB / VBA

I occasionally record some tech/programming notes for the greater good of the community. If you have no idea what this means, ignore it and move onto the posts on religion…

However, if you are trying to convert a number to Unix Timestamp in VB or VBA (ie in Access), here is how it works…

I have a field which historically stores dates as an integer YYYYMMDD ie 19671008 and I need to convert it to Unix format which is number of seconds after 1st Jan 1970. Ask Google and there is such a lot of useless stuff about Unix to VB, but nothing in the other direction.


Function converttoUnix(dt)

   If Len(dt) <> 8 Then dt = "19700101"

   y = Left(dt, 4)
   m = Mid(dt, 5, 2)
   d = Mid(dt, 7, 2)

   df = DateSerial(1970, 1, 1)
   dnow = DateSerial(y, m, d)

   converttoUnix = DateDiff("s", df, dnow)

End Function

Obviously, if you are converting a different format, you need to modify the lines before the DateSerial to get/extract the days, months and years. It might also be prudent to add a second to the final figure to ensure that the date is actually IN the right day and not at midnight.