We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
7 days, 20 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Excel 2010 changing numbers to "12:00 AM"
Posted on 2013-02-01
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3-
2
6 Comments
Unfortunately, your problem is a common one and seems to be without fix.
In order to prevent Excel from converting your numbers into dates you must enter them as Text. Individually, you can do that by preceding each entry with an apostrophe. The apostrophe will not be displayed but Excel will treat the number as text and, therefore, not change it into a date. Collectively, you can change the number format to Text (not General) for the entire column. Unfortunately, you have to do this before Excel has done its conversion trick.
In order to prevent Excel from converting your numbers into dates you must enter them as Text. Individually, you can do that by preceding each entry with an apostrophe. The apostrophe will not be displayed but Excel will treat the number as text and, therefore, not change it into a date. Collectively, you can change the number format to Text (not General) for the entire column. Unfortunately, you have to do this before Excel has done its conversion trick.
Active today
The numbers you describe are normal for dates. We may enter 2/1/13, but Excel sees it as 41306, which is the number of days that have past since 1/1/1900. If you enter 41306, then format it as date and time you will get 2/1/13 12:00:00 AM. Enter 41306.00001 and it becomes 2/1/13 12:00:01 AM. Everything left of the decimal is the date and everything right is the time. When you enter 2/1/13, Excel automatically formats that cell to a date. If you copy and paste in a different cell, that cell will become a date also. SO, have I seen this done on it's own, no. I'm guessing the columns were originally formatted as a date which is why they're displayed as such.
Flyster
Flyster
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.
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.
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Excel Macro update each line based on another worksheet | 2 | 53 | |
| Excel formula based on 2 different filters | 4 | 42 | |
| Excel formula to calculate ID # | 4 | 41 | |
| Resolving #VALUE error in spreadsheet | 2 | 16 |
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation)
David Miller (dlmille)
Synopsis
In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Suggested Courses
Course of the Month7 days, 20 hours left to enroll
737 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.