A timely reminder of the dreaded ‘leap year bug’

Days like today don’t come around very often, so it’s an appropriate time to remind everyone of the dreaded ‘leap year bug’.

What is the ‘leap year bug’?

Ever since Excel debuted for Windows in 1987, it has incorrectly recognised February 1900 as having 29 days.

However, it was not the first to do so.

At the time, Lotus 1-2-3 dominated the spreadsheet scene, and due to reasons best left to mystery and rumour, the program included this bug when it was originally released for DOS-based computers.

Microsoft felt compelled to retain it for compatibility reasons so anyone migrating to Excel could enjoy a smooth and seamless transition.

However, Excel for Mac bypassed the problem entirely thanks to its 1904 date system, which was the default up until Excel 2011, when 1900 took precedence.

How are dates stored?

Excel stores dates as serial numbers that count the elapsed days since 01/01/1900 or 01/01/1904, corresponding to the 1900 and 1904 date systems.

Both of these have a serial number of 1, which means the serial number for 1 January 1904 is 1462 in the 1900 system.

Any date that comes before either system’s starting date will produce a #VALUE error.

Does it matter anymore?

Well, with each passing year, the less problematic it is, as the majority of Excel users only deal with dates in the 20th and 21st centuries anyway.

Nevertheless, spare a thought for those historians, genealogists, archaeologists, and antique dealers who must resort to janky workarounds or alternative software!

I doubt the ‘leap year bug’ will ever be fixed as the demand simply isn’t there. Creating a new date system would take considerable time and cause a lot of disruption to existing workbooks. Needless to say, the Excel team has got better things to be getting on with anyway.

So, we’re stuck with it forever!