Apologies if this is redundant, I searched around for an answer on this but I just was having continued difficulty. Currently, I have open an excel spreadsheet, and am looking at a column that is giving me dates. However, there are two formats. The format MM/DD/YYYY and DD-MM-YYYY. However, when I highlight the entire column and select date, only the mm/dd/YYYY seem to change if I choose short or long term date. The dates in the dd-mm-YYYY do not respond no matter what. So far, I have attempted to click “data” and have tried text to columns, and that does not work. I have also tried replacing “-” with “/” but that won’t work either, and would still leave me the problem of the format being dd/mm/YYYY which is also incorrect still. For whatever reason, excel just does not recognize the dates in the dd-mm-YYYY and I am clueless as to why that is. Again, ultimately I just want all dates to be recognized and also in MM/DD/YYYY format. enter image description here
8
The dates that have remained strings are ones that Excel can’t parse as a valid date. Your computer is set to recognize US date format, so when it encounters a date where the first value is greater than 12 (i.e. it thinks the month value is 13 or above) then it fails and leaves it as text.
And, because of that, it’s swapping the month and day for dates that it thinks it can parse.
So 06-09-2024 is parsed as 2024-06-09 when it should be 2024-09-06. You’re corrupting your own data. Simply fixing the “text” dates is not sufficient.
Instead of opening the CSV directly, go to the Data tab and select From Text/CSV.
That’s bring you to the Power Query editor where you can load the CSV and tell the editor what format your fields are.
It’ll then import all of the dates correctly.
You need to convert the text dates to date values:
- In a separate column enter following formula, replacing
A1:A21with the correct range from your data
=LET(
dates, A1:A21,
IF(
ISNUMBER(dates),
dates,
DATE(RIGHT(dates, 4), MID(dates, 4, 2), LEFT(dates, 2))
)
)
- Copy the result
- Paste over the original column as values (Edit > Paste Special… > Values)
Check the cells with the dates indented to the left: these are likely not set as date type but are formatted as text, which is why they are not affected by the change.
Try selecting the column, go to ‘Cells properties’, choose the ‘Custom’ format type, and enter the desired format in the ‘Type’ box as the follow:
dd-mm-yyyy
This should work.
1