The original file is in Excel and the dates are correct when displayed in Numbers. But the dates are changed when I export back to Excel.
MacBook Air, OS X Mavericks (10.9)
If I recall, you need to set the default date format in Excel to whatever the other one is. Excel uses two different date formats. One is "1904".
Excel/Preferences/Calculation
How do I set defaults in Excel without having to buy Excel?
I guess I misunderstood. It sounded like you exported to Excel so I assumed you opened the file in Excel and found the dates to be wrong.
So...what did you do to discover the dates were wrong? I tried a few things and could not get Numbers or Excel to screw up the dates unless I open the document in Excel and specifically change the date setting. But that only holds for the one document and only if I save that change.
A customer sent me an Excel file. I had to make revisions in the quantities to be shipped, and I made those revisions using Numbers. To let my customer see my revisions, I exported back to Excel. Fortunately, in reviewing my changes, I noticed that the four shipping dates had been changed, i.e. from 12/1/2013 to 12/2/2017. I went back to Numbers and exported again to Excel, with the same results on multiple attempts.
And so, my questions:
1) How do I stop Numbers from screwing up the dates when I export?
2) How can I trust that Numbers has not screwed up other elements of the spreadsheet? This doesn't end well if my customer sees an order that conflicts with his intentions or if I forward an incorrect order to my supplier.
I am trying to recreate the problem but cannot. I created quite a few files in Excel 2011 for Mac. Some used the 1900 date format. Some used the 1904 format. Some were saved as xlsx files. Some were saved as xls files. I opened them all in Numbers 3.0 and exported them to xlsx and xls files. I then opened those files in Excel. No problems with any of them. Next I tried basically the same thing but using Numbers 2.3. Still no problem. So I took all those files to my wife's PC and opened them all using her relatively old version of Excel. I couldn't get the dates to mess up once.
Nonetheless, the only "fix" I know of is within Excel. When the file is open, the user needs to change the date system being used.
What version of Excel is your client using? I'm thinking it is pretty old, like maybe before Microsoft changed the date system Excel uses so it isn't even looking for that information in the file when it opens it.
I have one more thing I want to look at. I want to look at some of those files I exported and see if the date system was changed.
The date systems remained consistent throughout the exports and imports.
A file that I created in Numbers then exported to Excel had the 1904 date system. Excel 2011 uses the 1900 date system by default but understands the exported file used the other system and opened it correctly. I have noticed this with other documents I have created in Numbers then exported: if I try to copy/paste dates to another Excel document I get a warning about dates.
A file I created in Excel with the 1900 date system, imported into Numbers, made a small change in Numbers, then exported back to Excel retained the 1900 date system. That surprised me.
I've no idea what version of Excel was involved, but I'm skeptical that's relevant. The Excel spreadsheet reads just fine when opened in Numbers. It's not until Numbers exports the file into Excel that the corruption occurs. In other words, Numbers is creating the error, not Excel.
I am having the same problem with the year being changed from 2013 to 2017. I have tried different date formats (11/13/13, Nov 13,2013, November 13,2013) and when I send that Numbers file to my PC and open the file in Excel, make some changes (or not) then send the file back to Numbers the dates are all incorrect again as 2017 not 2013.
I am using a rather large spreadsheet and this is a real inconvenience to keep checking what the dates are showing and then have to change them when getting them back in Numbers.
I hope someone can help with this problem.
Excel uses two different date systems, 1900 and 1904. Excel stores each date as a serial number; the number of days since the starting date of Jan 1, 1900 or Jan 1, 1904. I assume there is a byte somewhere in the Excel file format that indicates which date system to use for the file. On a Mac the default date system is 1904 except in Excel 2011 (and later) where it is 1900. On PC's it is 1900.
From the descriptions given of the problem, it sounds like it is an import problem. Neither problem statement mentioned opening an exported file in Excel and seeing wrong dates, only that wrong dates are appearing in Numbers after importing an Excel file.
If Excel saves the file in 1900 format but Numbers uses the 1904 format when importing, the dates shown in Numbers will be 4 years later. 2013 will become 2017. I assume these incorrect dates would be exported back to Excel as 2017, not 2013.
It sounds like a bug in the import process. I wish I could reproduce it but I can't.
Has anyone found an answer to this problem of dates being changed on export to Excel? How can it be reversed without retyping the whole spreadsheet? It is an absolute pain in the @ss.
No, nobody has recommended a solution, other than to blame Excel. I don't find that reply terribly satisfying.
Tom Jindra wrote:
No, nobody has recommended a solution, other than to blame Excel. I don't find that reply terribly satisfying.
Hi,
I am sharing Numbers Spreadsheets to clients and when i do this i have to share the documents as Excel because the clients arent using MAC. When my spreadsheets are sent, they have --809 in all of the boxes that have sterling figures in them, so, a box that should read £450.00 actually reads £--809450. This has only started since i upgraded to the new numbers. ??? The autofil that has now been removed is really annoying too. Why would Apple change things that aren't broke??
Wow! I see that your original comment was that I should stop complaining.
Well, here's the reason for my complaining:
1) I spent a significant chunk of cash for a Mac, instead of a much less expensive PC. I figured the extra money was worth it, to avoid problems.
2) The export problem is not my only problem, but it is clearly my most serious problem. Doing business is a bit difficult when I can't rely on the spreadsheets that I'm sending to my customers and suppliers.
3) I have tried correcting the dates myself, repeatedly. Guess what? The export screws up the corrections, repeatedly. These are rather extensive spreadsheets, and I have no good way of knowing whether these are the only corruptions. I hope to God I don't have customers raising **** with me later this year for having screwed up their orders.
4) I initially posted looking for a solution nearly two months ago.
5) Obviously, I'm not the only person having this problem.
6) At this point, it appears the only solution is to spend more money, to install Excel, because Numbers is worthless if I can't trust it to leave my work intact.
I have no idea who you are, Badunit. I'm skeptical of finding your name in any phone book in the country. But my complaint is not with you, whoever you are. My complaint is with Apple Support. This stinks. And if you wish to take that personally, that's entirely on you.
It sounds like your best solution is to buy Micorsoft Office or to use one of the free open source clones of it. Yes, you will have wasted the $20 you spent on Numbers but at least you will have something that works for you and that you can trust.
Apple support personnel may scan through these forums but, by and large, you are talking with other users here. To provide feedback to Apple on Numbers try the menu item Numbers/Provide Numbers Feedback. I have sent my fair share of comments and bug reports recently. I have not sent one on date import/export because I have not been able to produce the problem. I've tried, as detailed above, but I haven't yet had it happen.
Why does Numbers change dates within the spreadsheet when I export to Excel?
