DateTime and Excel difference
Seems that DateTime module thinks 29.2.1900 didn't exist while Excel 2008 does. Which one is correct ? Well DateTime is OpenSource while Excel is payware. Think payware would be correct ? nope, DateTime is !
So the correct DateTime object for dates starting 1.3.1900 when using values from Excel is :
DateTime->new(year=>1900, month =>1, day => 1)
->add(days=>$THEVALUEFROMEXCEL-2)
Excel-- !
Financial use of Perl, Emacs and Objective-C
Yup =)
Ran into the same thing using Date::Simple ..
Wonder what the reason is.. Guess they got it wrong in the beginning and couldn't fix it.. hehe
The 1900 leap year bug originates from Lotus 123, which either deliberately (to simplify the calculation in limited RAM days) or unintentionally had it wrong. Excel then replicated this in order to import Lotus worksheets...
wow, didn't know that...
my immediate reaction : why the hell copy a bug while they could as well put the correction in the import and export procedures ?
I guess it wasn't that simple - keeping 29/02/1900 after importing a Lotus 123 file would make sense, but what about a worksheet created in Excel (with the correct leap year formula) and then exported - would any affected date calculations and figures have to change if you exported it as a Lotus 123 file? Might have been rather confusing to the users...
http://support.microsoft.com/kb/214326