VARIANT DATE to datetime string

This online calculator converts VARIANT DATE (double) value to datetime string.

Creative Commons Attribution/Share-Alike License 3.0 (Unported)

This content is licensed under Creative Commons Attribution/Share-Alike License 3.0 (Unported). That means you may freely redistribute or modify this content under the same license conditions and must attribute the original author by placing a hyperlink from your site to this work https://planetcalc.com/7027/. Also, please do not modify any references to the original work (if any) contained in this content.

A long long time ago, in software development...
There was a VARIANT type, which could hold either of many other datatype values (vartypes), see wikipedia. And one of the vartypes was VT_DATE, aka Variant date, aka DATE (double), aka OLE variant date. It was a datetime value represented as double - 8-byte floating-point number. And, BTW, everything about variant data type is still relevant today, except for the fact that hardcore Windows or COM/ActiveX/OLE programming does not look like mainstream nowadays.

But, sometimes, when you see something like 42842.370277778, chance are that it is variant date. And here is a simple calculator which takes double value, interprets it as variant date and outputs date and time encoded in this double value. Those who curious about format of variant date and history behind that can continue to read after the calculator.

PLANETCALC, VARIANT DATE to datetime string

VARIANT DATE to datetime string


About variant date format.

According to documentation, days are represented by whole number increments starting with 30 December 1899, midnight as time zero. Hour values are expressed as the absolute value of the fractional part of the number.

Yet, there is a catch. Although day values become negative before midnight on December 30, 1899, time-of-day values do not. For example, 6:00 AM is always represented by a fractional value 0.25 regardless of whether the integer representing the day is positive (after December 30, 1899) or negative (before December 30, 1899).

Due to this, value of -1.25 represents 12/29/1899 06:00 AM, and value of -1,30 represents 12/29/1899 07:12 AM. That is, while second double value is less than first double value, as date it is greater than first date.

Also, date values between -1.0 and 0.0 represent the same logical dates as their positive counterparts. That is, -0.5 and 0.5 is the same 12/30/1899 12:00.

You may also wonder,why variant date has such a strange zero point - 30 December 1899, as opposed to 1 Jabuary 1900 (SQL Server zero point) or 1 January 1970 (Unix/Javascript zero point). As wrote by Eric Lippert, "Actually, it turns out that this is to work around a bug in Lotus 1-2-3! The details are lost in the mists of time, but apparently Lotus 1-2-3 used this date format but their devs forgot that 1900 was not a leap year. Microsoft fixed this bug by moving day one back one day." This, and the other VT_DATE oddities are explained in his article, and it is a very fascinating reading for old developer like me. Enjoy.

URL copied to clipboard
Creative Commons Attribution/Share-Alike License 3.0 (Unported) PLANETCALC, VARIANT DATE to datetime string