February 4th, 2010
Dates tend to always be tricky things to convert, since there are so many different formats used in different software packages. Today we received a support request about converting Excel dates. This should have been straightforward, as Excel simply provides the number of days since January 1, 1900. So, for example:
20 - January 20, 1900 35981 - July 5, 1998 39341 - September 16, 2007
So, as we saw with the JD Edwards Conversion, we should simply need to use the following formula to convert it:
date(1900,1,1) + fieldname - 1
However, it turns out that Excel has a date bug from its very early days, due to an even earlier date bug in Lotus 1-2-3:
“When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3.
When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This assumption allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move worksheets from one program to the other.”
So, in order to properly convert, we need to account for this extra day. And, we get the final formula:
date(1900,1,1) + fieldname - 2
For further information on dates, please take a look at the other posts we've done on this topic in the past.