CODEWITHSUNDEEP
exceldate
RubenGeert
RubenGeert

Reputation: 2952

Is there a bug in Excel concerning dates?

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?

Upvotes: 8

Views: 3835

Answers (3)

IRTFM
IRTFM

Reputation: 263411

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.

Upvotes: 5

occulus
occulus

Reputation: 17014

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/

Upvotes: -1

mmmmmm
mmmmmm

Reputation: 32700

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.

Upvotes: 4

Related Questions