2012 date/time format error
Hello Excel Experts!
I have some CSV files I'm importing into Excel 2007.
There is a Date/Time field that when looked at as text is thus: "12/31/11 05:55:43 pm", "01/01/12 09:41:39 pm", etc.
In other words it is in MM/DD/YY HH:MM:SS AM/PM.
I'm having two problems with this.
first is that all of the 2012 dates show up incorrectly.
They are being interpreted as YYYY-MM-DD HH:MM AM/PM and show up as follows: "01/01/12 09:41:39 pm" shows up in the formula area as being 2001-01-12 09:41:39 PM and appears in the cell as 01/12/01
That's January 12, 2001 instead of the correct January 1, 2012.
All the dates for 2011 appear in Excel correctly.
They show up the same as the underlying text - MM/DD/YY HH:MM:SS AM/PM.
The cell formatting for this entire column is: mm/dd/yy hh:mm:ss
So, why is Excel interpreting the 2011 dates correctly and misinterpreting the 2012 dates?
The second problem I'm having is that I need to sort the entries chronologically.
So, aside from the 2012 dates showing up completely wrong, the AM/PM format is causing me problems.
Excel doesn't sort AM before PM.
Do I need to convert all of the times to 24-hour
time? If so, is there a quick and easy way to do that? If not, how do I get Excel to accurately sort MM/DD/YY HH:MM:SS AM/PM?
Solutions to the Problem 2012 date/time format error
I think this link is public: ...
Thanks, I got the file.
I have Excel 2007 and my system's regional settings is set to default as
I've opened the CSV through straight double-clicking out of Explorer and using
Ctrl+O to open the file directly into Excel, but unfortunately, I cannot reproduce your date import problems.
I can, however, suggest that you use Excel 2007's From Text command from the
Data tab's Get external data group.
First select Delimited on the first page and add Comma to the
Delimiters on the second.
On the third page, select the column of date information in the bottom
Data preview window and force Date: MDY as the Column data format.
With this set you should be able to click
OK in the lower right.
Your data should import correctly and the dates should respond to number formatting (Date or
Custom) and Data, Sort commands.
Manually editing the Windows registry to fix Error 2012 date/time format error
Caution: Unless you an advanced computer user, we DO NOT recommend editing the Windows registry manually. Using Registry Editor incorrectly can cause serious problems that may require you to reinstall Windows. We do not guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.
- Click the Start button.
- Type "command" in the search box... DO NOT hit ENTER yet!
- While holding CTRL-Shift on your keyboard, hit ENTER.
- You will be prompted with a permission dialog box.
- Click Yes.
- A black box will open with a blinking cursor.
- Type "regedit" and hit ENTER.
- In the Registry Editor, select the Error 0x9C-related key (eg. Windows Operating System) you want to back up.
- From the File menu, choose Export.
- In the Save In list, select the folder where you want to save the Windows Operating System backup key.
- In the File Name box, type a name for your backup file, such as "Windows Operating System Backup".
- In the Export Range box, be sure that "Selected branch" is selected.
- Click Save.
- The file is then saved with a .reg file extension.
- You now have a backup of your MACHINE_CHECK_EXCEPTION-related registry entry.
Recommended Method to Repair the Problem: 2012 date/time format error:
How to Fix 2012 date/time format error with SmartPCFixer?
1. Download Error Fixer . Install it on your system. Click Scan, and it will perform a scan for your computer. The junk files will be shown in the list.
2. After the scan is done, you can see the errors and problems need to be repaired. Click Fix All.
3. The Repair part is done, the speed of your computer will be much higher than before and the errors have been removed. You can also use other functions in SmartPCFixer. Like dll downloading, junk file cleaning and print spooler error repair.
Read More: Troubleshooter of Error: 32-bit internet explorer not working and adobe flash not working for 64-bit internet explorer,Fast Solution to Error: 350 p, 35 mb Word doc crashes after refreshing (F9),Tech Support: 64 bit open gl 1.1,Fast Solution to Error: 3 consent.exe files cannnot open start menu is it mal-ware?,How to Fix Problem - 32 bit problems: lagging start menu, but on 64 bit the jumplist very Smoooth?,20-22 minute boot-up times - What have I not tried?,15871 blockpage error message. I can't get into facebook. How can I get this blockpage removed?,3.46gb ram usable but 8gb installed,6x9 envelope BUG,40 second boot delay