> On Wed, 9 Feb 2011, Dmitrii Dimandt wrote: >> When I convert this file to a text format, I get this: >> >> 01/10/10 40,452 > > the 10th of January 2010 is about 40,450 days from 1st of January 1900, which > is how all excel dates actually get stored internally.... If you take a date > cell and reformat it as a number, that's what you'll see >
A-ha! (that was my moment of zen) > What I suspect is happening is that you don't have the right format applied > to your formula cells. Can you double check they're really set to format as a > date? > > This is actually an odd situation. For the original cell, A182: - MS Excel on Mac OS displays that the formatting is actually a date, shows it as a date everywhere — in the toolbar, during editing and so forth - Apple Numbers on MacOS displays that the formatting is a custom date/time format, shows it as a date everywhere — in the toolbar, during editing and so forth - OpenOffice 3.2.0 on Ubuntu 10.04 says that it is a custom format. Displays the value as a date. But in the toolbar and during editing displays it as 40452. If you change format to "date", it starts displaying everything correctly. However, if you save it as .xls and reopen it, the problem returns :) - If you use Python's xlrd, it shows that the cell value is 40452 and the cell format is date. - Tika converts the cell correctly However, for the cell that refers to A182 and has the same format: - MS Excel on Mac OS displays that the formatting is actually a date, and shows the cell as a date - Apple Numbers on MacOS displays that the formatting is a custom date/time format, shows it as a date - OpenOffice 3.2.0 on Ubuntu 10.04 says that it is a custom format. Displays the value as a date - If you use Python's xlrd, it shows that the cell value is 40452 and the cell format is date. - Tika converts the cell to 40452 So I guess that the problem is probably inherent in Excel itself and I pity Apple's develpers for getting this right. Because, on top of all things, 01/10/10 (which is October 10th, 2010 over here in Europe) is 40450 days from January 1st, 1900. *Not* 40452 days. According to Python at least. This is so weird, to say at least :( Great many thanks for the tip though!!!
