date2Num AX vs. Excel discrepancy, why?

This question is answered

I'm importing a date as an integer, converted in Excel simply by changing from General to Number. In AX 2009 I'm using date2Num to do the conversion. Now the odd thing I found is that these two conversions are out by two days!!! Am I going mad? For example http://msdn.microsoft.com/en-us/library/aa854690%28v=AX.50%29.aspx, says that 13/01/1901 returns 377, which it does in AX but in Excel 2010 it returns 379. What am I missing? Please be nice.

Verified Answer
  • I'm not sure but probably the answer can be found in one of Joel's articles:

    Joel Spolsky
    In most modern programming environments, dates are stored as real numbers. The integer part of the number is the number of days since some agreed-upon date in the past, called the epoch. In Excel, today's date, June 16, 2006, is stored as 38884, counting days where January 1st, 1900 is 1.
    I started working through the various date and time functions in Basic and the date and time functions in Excel, trying things out, when I noticed something strange in the Visual Basic documentation: Basic uses December 31, 1899 as the epoch instead of January 1, 1900, but for some reason, today's date was the same in Excel as it was in Basic. 
    Huh?
    I went to find an Excel developer who was old enough to remember why. Ed Fries seemed to know the answer.
    "Oh," he told me. "Check out February 28th, 1900."
    "It's 59," I said.
    "Now try March 1st."
    "It's 61!"
    "What happened to 60?" Ed asked.
    "February 29th. 1900 was a leap year! It's divisible by 4!"
    "Good guess, but no cigar," Ed said, and left me wondering for a while.
    Oops. I did some research. Years that are divisible by 100 are not leap years, unless they're also divisible by 400.
    1900 wasn't a leap year.
    "It's a bug in Excel!" I exclaimed.
    "Well, not really," said Ed. "We had to do it that way because we need to be able to import Lotus 123 worksheets."
    "So, it's a bug in Lotus 123?"
    "Yeah, but probably an intentional one. Lotus had to fit in 640K. That's not a lot of memory. If you ignore 1900, you can figure out if a given year is a leap year just by looking to see if the rightmost two bits are zero. That's really fast and easy. The Lotus guys probably figured it didn't matter to be wrong for those two months way in the past. It looks like the Basic guys wanted to be anal about those two months, so they moved the epoch one day back."
    "Aargh!" I said, and went off to study why there was a checkbox in the options dialog called 1904 Date System.

All Replies
  • I'm not sure but probably the answer can be found in one of Joel's articles:

    Joel Spolsky
    In most modern programming environments, dates are stored as real numbers. The integer part of the number is the number of days since some agreed-upon date in the past, called the epoch. In Excel, today's date, June 16, 2006, is stored as 38884, counting days where January 1st, 1900 is 1.
    I started working through the various date and time functions in Basic and the date and time functions in Excel, trying things out, when I noticed something strange in the Visual Basic documentation: Basic uses December 31, 1899 as the epoch instead of January 1, 1900, but for some reason, today's date was the same in Excel as it was in Basic. 
    Huh?
    I went to find an Excel developer who was old enough to remember why. Ed Fries seemed to know the answer.
    "Oh," he told me. "Check out February 28th, 1900."
    "It's 59," I said.
    "Now try March 1st."
    "It's 61!"
    "What happened to 60?" Ed asked.
    "February 29th. 1900 was a leap year! It's divisible by 4!"
    "Good guess, but no cigar," Ed said, and left me wondering for a while.
    Oops. I did some research. Years that are divisible by 100 are not leap years, unless they're also divisible by 400.
    1900 wasn't a leap year.
    "It's a bug in Excel!" I exclaimed.
    "Well, not really," said Ed. "We had to do it that way because we need to be able to import Lotus 123 worksheets."
    "So, it's a bug in Lotus 123?"
    "Yeah, but probably an intentional one. Lotus had to fit in 640K. That's not a lot of memory. If you ignore 1900, you can figure out if a given year is a leap year just by looking to see if the rightmost two bits are zero. That's really fast and easy. The Lotus guys probably figured it didn't matter to be wrong for those two months way in the past. It looks like the Basic guys wanted to be anal about those two months, so they moved the epoch one day back."
    "Aargh!" I said, and went off to study why there was a checkbox in the options dialog called 1904 Date System.

  • Thanks gloomie, that is the answer. Found some more info here: en.wikipedia.org/.../Epoch_%28reference_date%29.

  • I've run into the same problem :-) I read an Excel file via .NET (Microsoft.Office.Interop.Excel) and I've found that methods Value() and Value2() of the Microsoft.Office.Interop.Excel.Range class return values of different types: Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType::xlRangeValueDefault) returns System.DateTime with the correct date while Value2() returns System.Double that becomes two days greater then needed after conversion. So I've decided to stick with the Value() method.