Redmond, we have a problem…

Create a new spreadsheet using Microsoft Office 2013. 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.

Create Spreadsheet

Place cursor on first cell (A1), and insert the following date: February 1, 1900 (using only numbers, in your preferred date format).

Place Cursor on First Cell

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.

Insert February 1900 Dates

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).

Save As...

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.

Choose Open XML Strict

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.

Check the success of the save as operation

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).

Open the just saved spreadsheet

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.

Drag down to line 55

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.

Drag down to line 80

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).

Drag down to line 300,000

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…

Comments
  1. 3 years ago
  2. 3 years ago
  3. 3 years ago
  4. 3 years ago
  5. 3 years ago
%d bloggers like this: