8

Please don't ask how I stumbled upon this but please try the following:

  1. Type "60" into a cell in Excel.
  2. Set the data type to a date format
  3. The date displayed (in my case at least) is February 29th, 1900.

However, February 29th, 1900 never occurred since 1900 was not a leap year (see Wikipedia).

Is this a bug or am I missing something?

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
RubenGeert
  • 2,902
  • 6
  • 32
  • 50
  • 1
    So how did you stumble upon this? – Uwe Keim Dec 06 '12 at 08:28
  • 1
    @UweKeim: I was converting Excel data to SPSS via Python and saw that the dates in SPSS were not correct. Since I thought the problem was in SPSS's DATESUM function, I initially posted [here](http://spssx-discussion.1045642.n5.nabble.com/Excel-via-Python-to-SPSS-problem-with-DATESUM-function-tt5716635.html). The issue has been solved very nicely: we know the correct formula and we know why it's slightly different than expected. – RubenGeert Dec 07 '12 at 07:30
  • 1
    It may (or may not) be worth mentioning that, actually, 1900 *was* a leap year. (on the Julian calendar) – William Leara Jan 26 '17 at 03:06

3 Answers3

4

No, it is not a bug in Excel and yes you are missing something.

Excel implemented the dates to match with Lotus 1-2-3 for which compatibility was required at the time so was designed to have this error. Lotus 1-2-3 had the bug of thinking 1900 was a leap year.

See Microsoft's documentation for the reasons why not to fix it.

mmmmmm
  • 32,227
  • 27
  • 88
  • 117
  • 1
    I see, it's incorrect on purpose for backward compatibility. OK, problem solved. I know what's going on and I'll simply adjust my conversion formula to take this into account. – RubenGeert Dec 05 '12 at 12:11
3

Please. It is a bug. It was never documented in the original Excel for Windows specifications. The original Excel which was on the Mac years before it was on any Windows platform used a 1904-based date system. The original Excel authors did recognize the issue.

"Strictly speaking, that wasn't an issue with Excel 1.0, which ran only on Macs. The original version of the Mac (and Excel) supported dates starting in 1904, partly to avoid the issue with 1900 not being a leap year. It wasn't until Excel was ported to Windows (Excel 2.0) that the 1900 date system was introduced, so you can't blame any of us on the Excel 1.0 team for the decision (and so, of course, we wouldn't comment on it in the interview)." https://www.geekwire.com/2015/recalc-or-die-30-years-later-microsoft-excel-1-0-vets-recount-a-project-that-defied-the-odds/

The bug was also on MS-BASIC and Multiplan.

IRTFM
  • 258,963
  • 21
  • 364
  • 487
-1

It's not a bug - Excel accepts Feb 29th 1900 as a valid date. It's a historical issue.

Your 60 is being regarded as 'number of days since Jan 1 1900'.

More info:

http://polymathprogrammer.com/2009/10/26/the-leap-year-1900-bug-in-excel/

occulus
  • 16,959
  • 6
  • 53
  • 76