Forum Discussion
Cannot format cells with dates to mmddyyyy
Using Office 365 - I am copying a notepad document to Excel to manipulate the data to fixed with columns. Within the notepad document there are three columns that contain dates. When they are copied to the Excel file, the three columns that contain dates will not allow formatting to a mmddyyyy (or any date) format. When the cell format is changed, the column fills with mostly "countless" "#" signs and a few cells with a nonsense date like "3-26-4068."
I spent almost three hours with Microsoft support yesterday and the tech uninstalled and reinstalled Office, changed system settings, etc., etc. and nothing worked.
I have made these data conversions two times before without any issue.
2 Replies
Strange that something that has worked should now fail. Is importing the data as a formal ETL step using PowerQuery an option for you? That allows finer control over the conversion between dates with different localisations than is possible using the automatic conversion of text in Excel.
If you are stuck with importing directly to the grid, then preformatting the range to text can block the automatic conversion, leaving you to sort the results using text manipulation formulas. Text to columns can also provide some conversion support.
As Harun24HR says, a sample of the data would be useful, and the problem would be easiest solved by someone using US/Liberia date settings on their computer.
May be problem with your system date/time settings. Excel can't recognize those values as date. Can you put few samples or screenshots for better prestation?
