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…
“Microsoft choice, here, is to replace February 29, 1900, with January 31, 1900 (which now shows inside the first cell (A1).”
That doesn’t describe the screenshot I see. As far as I can tell, Feb 29th has been replaced by Feb28th, and Feb 28th has been replaced by Feb 27th, etc… – basically they subtracted 1 day from *every* dates from 01/02/1900 to 28/02/1900.
That seems like MS is adjusting it’s internal representation of dates of “fractional days since 1 Jan 1900” and subtracting 1 to make all dates *after* 01/03/1900 to be correct in the absense of 29/01/1900, and fudging dates before 28/01/1900 in order to keep things like “A23 and A30 are on the same day of the week” consistent.
Because if you do the “right” thing and simply scrub 29/02/1900 with #error, and leave all other dates the same, then suddenly difference between dates across the gap is inconsistent, and the day of the week changes for all dates before then, which might be more of a problem for some spreadsheets.
They’ve certainly painted themselves into an awkward corner.
(I can’t read the dates on the last 3 screenshots though, so not sure what’s happening there.)
Out of curiosity, what does Libreoffice do with an “.xls”, or “.xlsx”-transitional file that has dates before, and including, 29/02/1900?
(I don’t have a copy of Excel to hand with which to create a suitable test document)
It would be interesting to imagine a scenario in which this problem could be relevant.
“It would be interesting to imagine a scenario in which this problem could be relevant.”
Hum are you serious?
Are you sure You have not select the range A1-A30 and Thiene duplicated it instead selecting A30 and drag it down alone?