wrong date in numbers

What do you see instead? What do you mean by "cannot see the date"? Is nothing there at all? Is it a large number?

There have been quite a few reports of problems with dates when importing/exporting from Excel and its open-source clones with Numbers 3.0. I do not have a solution to suggest other than to convert the dates in another column then "paste values" back into the correct column.

41586 would be the date 11/8/13 (or 11/9/2017). Excel saves dates as serial numbers and it uses two different systems. In the 1900 date system, #1 is Jan 1, 1900 and that is the first date in the system. In the 1904 date system, zero is Jan 1, 1904 and that is the first date in that system. On oddity about the 1900 system is that Excel long ago also coded in a bug so as to be compatibile with another app that thought there was a leap year in 1900.

The 1900 date system can be converted as (with the serial number in cell B2):

="1/1/1900"+B2−1−IF(B2>59,1,0)

Badunit's solution works on my MacBook but I'm not about to convert all my XLS spreadsheets since we share them with others that use Windows. What was Apple thinking when they decided they didn't have to allow imports of Excel sheets with the common date formats mm/dd/yy? I couldn't figure out why they left Numbers 9 on my laptop when I "upgraded" to the latest version. I deleted Numbers 9 before I found out Numbers 3.2 didn't work! G'rrrr

I think it is something else going on besides the date format mm/dd/yy. I can import that format just fine. In fact, I have yet to run into the date import problem (but i don't import that many Excel spreadsheets). I created an Excel spreadsheet in Excel for Mac 2011 that used every built-in date format and I imported it into Numbers 3.2. Not a single one of those dates imported as a serial number. I have not tried doing the same with a PC version of Excel, though.

I haven't tried it on an IOS device but we have a MacBook and two iMacs all running version 10.9.4 and Numbers 3.2. They all fail to display the date correctly.

If you have a sample Excel file you can email to me, I would love to see it. I've read about this problem for so long but never been able to duplicate it.

OK. I'll dig out my old Windows laptop and open one of the xls sheets so I can delete the private info. Then I'll send you the sheet. Give me a day or two since the laptop hasn't been on for a couple years!

Please see my email and Photo Share.

No need to send photos. I was hoping to get an actual Excel file that would get messed up on import. As you have seen when you tried to cull out the personal data and it magically started working correctly, it seems to be more complicated than at first glance.

OK. I think I figured it out. If I simply open the xls database in my old Excel (2002) app on my Dell laptop, save it with the same file name and no changes then open that on my iMac with Numbers 3.2, it opens OK and the dates are fine. So something is changed by opening it then saving again without any changes. I called the guy that creates the database to see if he upgraded his Windows PC or Excel and it turns out he didn't but he creates the database in Open Office 4.0.1 then exports it as a *.xls file format!

So what is different is that opening the database in Excel then saving it must be cleaning up something that didn't bother Numbers 9 but doesn't work with Numbers 3.2.

I've tried a number of variations of editing, opening and saving the original xls file and it doesn't matter what I do, it will always open OK in Numbers 3.2 as long as it has been opened and saved once in Excel. Screwy problem.

With this and a few other problems (like import of CSV files), I get the feeling that the new design team tried to streamline the old code but didn't understand why it was written the way it was.

Thanks. I received your file. No one previously mentioned the blue triangle warning message in Numbers 3 that says the format could not be imported and what those formats were (or I missed it if someone mentioned it).

If I import the file into Numbers 3 and look at the blue triangle warnings, there are two slightly different formats neither of which is one of Excel's built-in date formats. They are custom formats of mm/dd/yy and mm/dd/yy;@. Nonetheless, "mm/dd/yy" is a dead giveaway and should have been seen as a date during import. It works fine in Numbers '09.

If I save the Excel file under a different name in Excel without making any changes to the spreadsheet, the new version imports correctly. This leads me to believe that Excel interpreted the original file and corrected it.

I'll have to think about what else I can try out to figure this out. Right now it is dinner time.