esu4236
asked on
Excel 2010 changing numbers to "12:00 AM"
Has anyone ever had a problem where Excel 2010 will change numbers that may be formatted as General, Currency, Dates or Times just change all the numbers so they display as "12:00 AM" and formatted as Time? And then when you look in the formula bar, it will display both a date and time, but they're dates like 4/12/1909 or 8/24/2139. It's just goofy. So then you highlight the entire column, and change the formatting to GENERAL, and the original numbers that were typed in display just fine. Why is this happening? It's happening on more than one document, and the user says it's been happening since she's been upgraded to Excel 2010. Some of her data is fine, and then others are not. She is saving her files into the new .xlsx format. Any suggestions?????
I've attached a file so you can see what I'm talking about. Look at columns I and J. The original file had more worksheets in it, but I deleted those.
Experts-exchange-file.xlsx
I've attached a file so you can see what I'm talking about. Look at columns I and J. The original file had more worksheets in it, but I deleted those.
Experts-exchange-file.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It isn't necessary for the cells to have been formatted as Date previously. Rather, the unwanted change seems associated with the upgrade to a newer version of Excel.
ASKER
Yes, it definitely started happening after the upgrade. The numbers were probably formatted as just GENERAL previous to the update, but then the system is changing them to a date format.
I am aware that Excel stores dates and times as serial numbers. I really don't believe these columns were formatted as dates before.
So nothing else we can do but to change the format back to General or Text?
I am aware that Excel stores dates and times as serial numbers. I really don't believe these columns were formatted as dates before.
So nothing else we can do but to change the format back to General or Text?
If you have access to the original files you can format the columns to Text and THEN convert to a newer Excel version. The gurus say that this would prevent the conversion. If it doesn't there are other means of doing the same thing. However, once the data have been "converted" they have, effectively, been destroyed and can't be recovered.
So, I should ask the question: Is your problem that you lost data and want them back? The answer is no. Is your problem that you still have the data but can't use them in the newer version of Excel? Try formatting as Text and see if that helps. If it doesn't help, insert a helper column, convert the data using the TEXT() function, replace the formulas by values (Copy/PasteSpecial: Values) and then replace the original column with the helper column.
So, I should ask the question: Is your problem that you lost data and want them back? The answer is no. Is your problem that you still have the data but can't use them in the newer version of Excel? Try formatting as Text and see if that helps. If it doesn't help, insert a helper column, convert the data using the TEXT() function, replace the formulas by values (Copy/PasteSpecial: Values) and then replace the original column with the helper column.
ASKER
Thanks for the input on this!
Flyster