According to Microsoft, dates begin at 1900
A simple test:
- Find a copy of Microsoft Excel
- Format the fist column as a date
- Type 18/12/1901 in the first cell. Excel will recognise that as 18 Dec 1901
- Type 18/12/1564 in the next cell down. Excel will think this is a string and leave it as it is
Go figure…
Luckily, Open Office realises that history goes back further than 1900. The Open Malaysia blog goes into the details of this, and I don’t claim to understand the whole story but the upshot of all of it is that this is an application level restriction that affects the native Office formats rather than the xml export formats, and it is being proposed as an ISO standard to come into force in September.
I can’t put it better than the Open Malaysia blog:
“…please avoid the native file formats of [Microsoft Office] if you are a Islamic historian, Renaissance archivist, Medieval coin collector, or someone who just has to work with dates prior to the 20th Century. “
Best stick with Open Office then!
Actually it is much worse than first appears. For those of you with a full copy of Microsoft office, create a spreadsheet with a column of numbers running from say 100 down to -1000. Format the cells as dates and all the positive numbers will display correctly as dates down to 1/1/1900 but the negative numbers will not. None of the functions like year(), month() will work on the negative numbers.
Now for the fun. Save the spreadsheet, fire up Access and link or import the spreadsheet you just created. The entire column comes in as dates even prior to 1900! Same company - different results.
Bruce
What version of Excel are you working on? I’m on Excel 2002 and do not have this problem maybe you should clarify which version of Excel you tried this out on.
Okay I’ll retract that statement, I was mistaken, you are correct, however like OS there are busy bees coming up with workarounds if you want to look for them.
I agree it’s weird they can’t fix this.
I tried on Excel 97 and 2003 but was reliably informed it happens even with 2007. I guess I’m a little embarrassed that as an archaeologist (and thereby working with historical dates quite a lot) I had never noticed this before!
There is a nice free add-in called xdate available at:
http://j-walk.com/ss/excel/files/xdate.htm
Works well and the price is right.
I also specifically asked the Microsoft team last year about this and their response was they haven’t even thought about it.
Bruce