iPhone displays incorrect Excel & OpenOffice date values

Question

Level 1

5 points

iPhone displays incorrect Excel & OpenOffice date values

An odd one, this, that I've had on various versions of iOS and on at least two different phones (a 4 and a 3).

I have a few spreadsheets - nothing large or complex, around the 30 KB mark in size without any custom macros, code or complex formulas.

These were originally created in either Excel 2003 or Excel 2007 (on a PC) and later edited in OpenOffice/LibreOffice (on a Mac), and all saved as .xls files.

However the date field values in every single spreadsheet display incorrectly. The YY element is correct; the MM element is correct; but the DD element is just plain wrong, and typically displays as a three-digit value (like "237/08/1987"). On a PC or a Mac the data displays correctly; it's only on my iPhone this happens.

I've tried creating new spreadsheets in OpenOffice/LibreOffice from scratch and using the default date formats. I've tried different formats. I've tried switching US and UK formats. Nothing seems to help. The problem appears with the default renderer (i.e. the one used when you open an attachment or view a file in the Dropbox app). It doesn't appear when using a third party editor, like the QuickOffice app.

The only solution seems to be to create and edit the files using Excel only, or only to view files in QuickOffice, which for various reasons are non-starters.

Any thoughts? Is this a bug in OpenOffice and LibreOffice? Or a bug in the way the iPhone is handling these files? (Since other mobile and desktop apps don't have this problem.)

iPhone 4, iOS 5.1.1

Posted on Sep 7, 2012 6:12 AM

Reply

Answer 1

Mar 5, 2013 7:09 AM in response to Crisiomiro

This is a problem with the iOS built in parser for xls files. I also have it on my iPhone 3GS with iOS 6.1.2. I see it with xls files accessed via DropBox, and with xls files viewed from within Mail. It's not much help to you, I know, but the DD number is, I think, the number of that day in the year so 28th Feb displays as 59/02/2013 etc.

Reply

Answer 2

Crisiomiro Author

Level 1

5 points

Mar 5, 2013 7:23 AM in response to James Richards

Thanks James, that's certainly an interesting explanation.

The parser seems to struggle with Office formats generally. I switched to using xlsx files and the date format issue disappeared, to be replaced with two new issues: dates are displayed as a five digit string and currency and decimal fields ignore formats. (That is, the fields are displayed as 'raw' numbers with multiple decimal places regardless of the format or decimal place setting actually selected.)

I think this is something just to live with!

Reply

Answer 3

Mar 5, 2013 7:38 AM in response to Crisiomiro

I can at least in part, confirm James Richards' comments. The US format mm/dd/yyyy and the UK format dd/mm/yyyy appear to work fine. dd-mmm-yyyy (05-mar-2013) has an issue in DropBox, GoodReader, and ShareFile (but is fine in Numbers). I'm sure it is impacting other date formats as well. Not surprisingly, the iPad does this as well.

iOS does have an issue with some date functions (actually it in the underlying UNIX), impacting calendar and similer types of apps. I assume this is related to that. But aas is evindenced by spreadsheet applications, it is possible for an app to overcome it.

Sorry- I don't have a workaround, but would suggest reporting the issue to Apple: http://www.apple.com/feedback/ and perhaps the vendors for any other apps you see it in.

Reply