Date Format Bug?

Question

Level 4

1,397 points

Date Format Bug?

I noticed a problem with Numbers Date function/format.

I created a spreadsheet in Numbers with some dates, in the format mm/dd/year.

I exported this spreadsheet to an xls format, and took a look at it with OpenOffice. The spreadsheet appeared to be fine.

I editied one cell in OpenOffice, and re-saved it.

Upon opening the edited spreadsheet in Numbers, the dates had converted to a number format, and could not be re-formatted into a date format...

Looking at other Excel-created spreadsheets, I'm getting the same result in Numbers: Date formats changing in Numbers to an unrecognized format.

Anyone else experencing this? Workarounds? Fixes?

Thanks,

Bob

(Using the newest version of Numbers, and latest version of OpenOffice as well... All spreadsheets are in the xls format when input into Numbers)

iMac, OS X Mavericks (10.9), Mac Plus, MacBook Pro

Posted on Dec 5, 2013 10:56 AM

Reply

Answer 1

Badunit

Level 7

30,960 points

Dec 6, 2013 3:23 PM in response to Bob Gold

I have not seen that.

Excel saves dates as serial numbers; the number of days since Jan 1, 1900 or 1904 depending on which date system is being used in the document. I am assuming the numbers you see are the serial numbers. You might be able to recover your dates by adding them to one of those two dates.

Reply

Answer 2

Bob Gold Author

Level 4

1,397 points

Dec 6, 2013 4:47 PM in response to Badunit

Badunit,

Thanks for your thoughts... The numbers do appear as serial numbers, and I've puttered around with them a bit.

I've sent Apple feedback about this issue... Unless I've got something unique with my three computers and their copies of Numbers, this will end up being a problem for anyone who receives an excel spreadsheet that happens to have a cell with a manually inputted date.

If the excel spreadsheet cell was created using a date formula (versus just formatting a cell as a date, and inputing a number), this anomaly doesn't seem to manifest itself.

Would you do me a favor? If you can create an excel spreadsheet using Excel or OpenOffice, would you see if you can replicate my issue? Just create a spreadsheet with one cell, formatted as a date, and input the date 1/1/2013. Save it, and then open it with Numbers, and let me know what the result is...

Thanks!

Bob

Reply

Answer 3

Badunit

Level 7

30,960 points

Dec 6, 2013 5:07 PM in response to Bob Gold

I have played around with dates trying to replicate a problem where they get changed by 4 years (something related to the Excel 1900 vs 1904 date system). No problem I can find here. I also did what you asked. I even tried formatting it with different types of date formats and saving the file as xlsx as well as xls. I then tried starting it in Numbers then exporting to Excel, editing the cell, saving, and importing it back into Numbers. I cannot duplicate the problem.

Reply

Answer 4

Bob Gold Author

Level 4

1,397 points

Dec 6, 2013 5:14 PM in response to Badunit

Badunit,

Ok...thanks for experimenting with this for me... I'm thinking I've got something odd with my copy of numbers (downloaded to one computer an migrated it to two other computers...)

I'm thinking I'll delete numbers, and re-download it, to see if that fixes the problem...

Bob

Reply

Answer 5

Bob Gold Author

Level 4

1,397 points

Dec 7, 2013 9:51 AM in response to Badunit

Badunit,

I deleted and re-downloaded and installed Numbers... Still has the same odd behavior with Date Formats... More experimenting to come...

Bob

Reply

Answer 6

Badunit

Level 7

30,960 points

Dec 7, 2013 10:54 AM in response to Bob Gold

Your first test included OpenOffice. You mentioned "other Excel-created spreadsheets". Were these other spreadsheets created in Excel, not OpenOffice? Did they go through OpenOffice at some point in time? What version(s) of Excel? I have Excel 2011 for Mac and Excel 2007 on a PC. Neither has given me any problems so far. I do not have OpenOffice so I won't be able to test with it.

Reply

Answer 7

Bob Gold Author

Level 4

1,397 points

Dec 7, 2013 11:48 AM in response to Badunit

Hummm....

I dug out an old Excel spreadsheet created with Excel 2004 for Mac, and took a look at it in Numbers... No problem with the time/date formatting...

Opened it in OpenOffice 4.0, and edited one cell, and the Numbers version has problems with the time/date format... Looks like the problem might be with OpenOffice...(crap!)

thanks

Bob

Reply

Answer 8

Badunit

Level 7

30,960 points

Dec 7, 2013 12:57 PM in response to Bob Gold

OpenOffice 4.0.1?

Reply

Answer 9

Bob Gold Author

Level 4

1,397 points

Dec 7, 2013 6:37 PM in response to Badunit

Yes... Trouble with 3.4.1 as well, apparently...

Bob

Reply

Answer 10

Bob Gold Author

Level 4

1,397 points

Dec 13, 2013 5:04 PM in response to Badunit

Update:

Apple's iWork folks emailed me, requested example files, and appear to be looking into this issue...

Bob

Reply

Answer 11

Badunit

Level 7

30,960 points

Dec 13, 2013 5:32 PM in response to Bob Gold

That's nice to hear.

Reply

Answer 12

Bob Gold Author

Level 4

1,397 points

Dec 13, 2013 5:46 PM in response to Badunit

Do you happen to know what "date base" Apple Numers uses? I've started to experiment with changing OpenOffice's preferences, but am still having a date conversion problem between the two programs...

The OpenOffice help menu indicates that Apple uses 1/1/1904 as a base (but when I switch OpenOffice to that base, it doesn't correct the Numbers problem). From the OpenOffice Help Menu:

Date base for day zero

Dates are calculated as offsets from a starting day zero. You can set the day zero to be one of the following:

Date base	Use
'12/30/1899'	(default)
'01/01/1900'	(used in former StarCalc 1.0)
'01/01/1904'	(used in Apple software)

Choose OpenOffice - Preferences - OpenOffice Calc - Calculate to select the date base.

When you copy and paste cells containing date values between different spreadsheets, both spreadsheet documents must be set to the same date base. If date bases differ, the displayed date values will change!

Bob

Reply

Answer 13

Bob Gold Author

Level 4

1,397 points

Dec 13, 2013 6:57 PM in response to Badunit

Just found this thread:

https://discussions.apple.com/thread/4002575?answerId=18559682022#18559682022

There is nothing wrong with the import, except that I don't believe that Numbers has the abiilty to infer the date from a number or string that looks like a number, without doing some date/time math.

Assume for this example that your date values are in column A. To convert to Date format, add a column formatted as Date/Time, with this expression:

="1/1/1904" + A

1/1/1904 is the base date in the Numbers system.

The addition will give you the base date plus the offset (your data) resulting in the proper outcome.

Regards,

Jerry

I created another column in my spreadsheet, and included the formula that Jerry (above) references. All dates are now correct in the Numbers spreadsheet (at least, in the column I created)... Now, why Numbers would automatically convert an excel-formatted date is a mystery to me...

Bob

Reply

Answer 14

Badunit

Level 7

30,960 points

Dec 13, 2013 8:51 PM in response to Bob Gold

It isn't just you with these date problems. Numbers apparently isn't understanding that these cells are supposed to be dates.

The problem Jerry addressed was different from this new problem, though the conversion formula works for both problems. The problem the other guy was having was that the cell had a formula in it that resulted in a number. In Excel I guess you can format that cell as a date but in Numbers, you cannot format a number as a date so all he was getting was a number.

Reply

Answer 15

Bob Gold Author

Level 4

1,397 points

Dec 13, 2013 9:16 PM in response to Badunit

Topping it off, Numbers requires quotation marks around the "date" ("1/1/1904") in the formula, in order to give me a proper result.

If I export the spreadsheet with the above formula back to an Excel format, OpenOffice can't properly interpret the result (OpenOffice doesn't know what to do with the quotation marks)...

The above problem, coupled with the problem of being unable to email Numbers (as well as Pages and Keynote) files through Gmail, without first converting them to Excel, Word, or PPT formats is (in my opinion) a reason to drop Apple's software, and go with the industry standard... It's a mess to try to help folks with their Excel spreadsheets, when Numbers isn't calculating every cell properly...

I guess we'll see what future updates bring...

Bob

Reply