Convert date to UNIX timestamp excel vba

Question

I have found a UDF that converts that date to UNIX timestamp

Sub Test()
Debug.Print ToUnix("03/01/2012")  '1325548800
End Sub

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

The result is 1325548800 in the immediate window

And I have this URL for yahoo finance LINK

If you see the filter options you will find the date 3/1/2012 (3 Jan 2012) and in the url you will find the unix timestamp 1325566800 which is little different from the result of the udf How can I get the same unix timestamp existing in the URL provided?

Deepstop · Accepted Answer · 2019-07-19 13:45:23Z

3

Those two timestamps are exactly 5 hours apart. I expect that Yahoo Finance is in a different time zone to the system you are working on. If Yahoo Finance is the only site you care about, then you could simply add 18000 seconds to the output of the function, although this may well change when daylight savings time ends in November.

VBA will calculate the difference between dates in your own time zone. This is only equal to the Unix date in the UTC time zone. You can test this by executing in an immediate window.

print datediff("s", "1/1/1970", date) mod 86400

86400 is the number of seconds in a day, and the remainder is 0, meaning the calculation is from midnight local time on Jan 1, 1970 to midnight today. If you take the remainder of the yahoo time,

print 1325566800 mod 86400

The result is 18000, which is the number of seconds in 5 hours. So the Yahoo server is in time zone UTC-5. With Yahoo Finance in New York City, it is only 5 hours behind UTC during the Winter. From March to November, it is four hours behind UTC because New York City observes daylight savings time.

However, I suspect for consistency their time stamps are all UTC-5, as any test run recently would otherwise show UTC-4. My caution to you is to observe what happens when NYC switches from Daylight Time to Standard Time in November, just in case.

edited Jul 19, 2019 at 13:45

answered Jul 19, 2019 at 13:19

Deepstop

3,8372 gold badges12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

YasserKhalil Over a year ago

Thanks a lot. I am confused with the last part in your answer `this may well change when daylight savings time ends in November.' I am just case about Yahoo in this case .. I will have dates on my sheet and I would like to convert to UNIX to be exact as on the Yahoo URL

Sam Over a year ago

@YasserKhalil - UNIX timestamps does not care about daylight savings time, but you function is based on a clock that does. At some point you will have to find another source for time, or simply compensate.

Deepstop Over a year ago

@YasserKhalil I added some further explanation to the answer.

Collectives™ on Stack Overflow

Convert date to UNIX timestamp excel vba

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related