What
Sometimes Excel Data formatting get stubborn, no matter what you try it just does not work and things get frustrating.
This usually happens when the dates values are pasted in from applications other than Excel and are stored as text.
In some cases, this behaviour appears after re-installing the operative system.
How
The simplest thing to try is to double-check that the range where the dates are stored is formatted as a date. If they are already formatted as a date, you need to ensure that the dates are actual values. Normally Excel warns the user about it, but there are instances where it seems like no matter what you try, the display format doesn’t change.
This post will let you solve these specific cases.
1 – First, make a copy of the file or the range you wish to solve
2 – Before to perform the actual conversion, it is important to set all dates in the range in the same visual format, meaning all following the same style, for example, Day-Month-Year. We will instruct excel on how to read the dates from the range, and if in the range we have some date in DMY, other in MDY etc., will return day that is to be read as DMY in the other format and vice versa. The dates should all be as below:
3 – You can watch this short clip or continue reading below if you are a reading person. It the post, there is more information.
Once you have formatted the range in the date format you want, select the range and click “Text to Columns” in the “Data” tab.
In the wizard leave everything in default and click “Next” twice.
In the 3rd step change the data format to “Date” and select the format in which the dates are shown in the cells. This will let the software understand how to read and format the dates.
In the example “DMY” which stand for Day, Month and Year, Excel now knows that the digits at the left of the delimiter represent the day of the month, the digits in between represents the month and the digits at the end represent the year.
Before to click finish you can see the preview.
Once the conversion is done, the date might still be not exactly as you want, therefore you can re-apply the format to the range.
Go to the “number” tab and select “More Number Formats…” at the bottom of the dropdown.
In the popup window, you can select the format that suits your needs or even create a custom one.
You should hand up what you wanted.
