Redmond, we have a problem…

Create a new spreadsheet using Microsoft Office 2013 or Microsoft Office for Mac. I am using the Italian version on one of the very first ASUS ZenBooks, running Windows 7 (fully updated) with 4GB of RAM and 128GB of SSD. If you need Microsoft Office you can see how to get ms office for cheap here. Place cursor on first cell (A1), and insert the following date: February 1, 1900 (using only numbers, in your preferred date format).

Drag down the right handle until line 30 (A30) in order to fill automaticall the cells with the dates. You should see February 29, 1900, the day that never existed (but was introduced into spreadsheets by Visicalc and then maintained by Lotus and Microsoft). After February 29, 1900, you will of course see March 1, 1900, then March 2, 1900, then… etcetera.

Save the file using the “Save as…” function, as you want to use the OOXML Strict format (while the default one is OOXML 2013 Transitional).

Please be careful in choosing “Spreadsheet Open XML Strict”. The entry is almost at the end of the list (which is slightly odd, as it should be the first one because of its standard nature). Then close the document, then close Microsoft Excel.

Double check the success of the operation. I gave the document the test.xlsx name. Unfortunately, you cannot distinguish an Open XML Strict from an Open XML Transitional from the icon or from the extension.

Launch Microsoft Excel, and open the text.xlsx spreadsheet. You will immediately realize that February 29, 1900 has eventually disappeared (the date has never existed, so this should have always been the norm).

Microsoft choice, here, is to shift down the cells to replace the now missing February 29, 1900. Because of this choice, inside the first cell (A1) there is now January 31, 1900 (which has never been inserted in the document).

At this point, you might want to know what there is after March 1, 1900… Because of this, you drag down the same right handle until line 55. Funny enough, you discover that after March 1, 1900, there is January 31, 1900, followed by the month of February (which is just before March 1, 1900). Weird, and somehow embarassing.

Of course, you start wondering about the format… So, you drag down the right handle until line 80, with the same weird – or embarassing – results.

Well, it definitely looks like Excel has some subtsantial problems in handling Open XML Strict spreadsheets. So, you want to see if the situation changes at all by drawing the right handle down to line 300,000 (yes, three hundred thousand).

Redmond, we have a problem… Let’s list the last ten dates: February 27, February 28, February 14, March 2, March 3, March 4, March 5, February 14, February 15, and of course February 16… Redmond, we definitely have a problem…

Or better, Redmond, YOU definitely have a problem…