How is "variant time" (DATE, double, 8-byte) handled?

Question

I can't seem to find any information on how a "variant time" (DATE, double, 8-byte variable) is handled.... I have a variant time "A" which value is "41716.892329". If I convert "A" using "VariantTimeToSystemTime" (or "COleDateTime") - I get "2014-03-18 21:24:57".

• How is this variant time calculated?
• Is it capable of storing milliseconds?
• Is there any way to determine if variant time is an AM or PM time?

I'm a bit confused regarding the AM/PM thing because the device that I'm working with at that moment was set to "09:24:57" (AM) and not "21:24:57" (PM).

Can this be a problem of the device SDK that provides me with an incorrect variant time?

EDIT: This was a problem of incorrect timezone set in the device (was set to "GMT-12:00" instead of "GMT-00:00")

Thanks.

Answer 1

The "variant time" (DATE) is almost but not quite the same as an Excel Date. Excel (because it was originally competing with Lotus-123) copied the way Lotus stored Dates (for compatibility with existing users). Unfortunately, Lotus had a bug (possibly deliberate, see Joel Spolsky's blog) which meant that February 1900 had 29 days. As such, Excel also thinks that February 1900 has 29 days (still). Try formatting the value 60 as a date. However, a 'variant time' is used with OLE Automation (not just inside Excel) and was fixed so that it could correctly represent any point in time, originally between January 1, 1753 and December 31, 2078 (ca. 2006) but now between January 1, 100 and December 31, 9999 (12/05/2018) such that a time difference could be obtained by a simple (possibly two part, see below) subtraction.

Eric Lippert has a comprehensive analysis but a summary is:

The "variant time" is a double where the integer part is the number of days after 30dec1899. As such, 0 is 30dec1899 while 01jan1900 is actually 2 (unlike Excel which copies Lotus and defines it as day 1) and there is no 29feb1900. The fractional part of the decimal string representation is treated as an unsigned partial day offset from 00:00:00 on that day. The significance of the wording here is that while numerically -1.75 is the same as -1 + -0.75, a variant time treats -1.75 as -1 + +0.75 (i.e. 3/4 of a day after midnight 29dec1899). Thus, when converting negative values to YYYY/MM/DD HH:MM:SS or calculating date differences involving negative values, the date and time parts must be treated separately.