Mixed date format

[maounde]

Hello, A variation on this question My array is mixing up the format of my dates, and I can't work out why

I have downloaded some data that contain in the same column 2 types of date format:

13-05-2022 05:49:58
and
12/5/2022 10:53:48 AM

Not sure why.
Formatting the whole column as date does not change the actual content of the cell. This seems to trip up any analysis with Pivot chart

Any suggestion to have a unique date format in the column?
Thanks

 

[Alex Blakenburg]

Formatting the whole column as date does not change the actual content of the cell.

This would indicate your dates are not being recognised as dates but are being seen as text.
What is your local date format ?
How are you getting the data into the spreadsheet ?

 

[maounde]

This would indicate your dates are not being recognised as dates but are being seen as text.
What is your local date format ?
How are you getting the data into the spreadsheet ?

Thanks for the quick reply. For context, I downloaded the data into as CSV from an enterprise online community, not unlike MrExcel. Each download for a month has about 70-80k rows and 42 columns. I have no control upstream over the format.
I opened a CSV of a specific month, saved it as XL. Then I converted it with a table.
Because I have several sheets like that to combine, I tried Data/get Data ... From table/Range.
That when I saw this:

So I tried to convert the whole column and got this:

Back to the sheet to investigate, I saw that some dates had a different format as I showed.

I tried to format the date from the Home tab / Number menu. Short date, long date, custom... nothing works.

My local date format: not sure how to check. I am located in the US. I don't remember changing anything about the default parameters on date. my windows system displays 6/1/2022, like that

I hope the context clarifies the question. Do you see an easy way to convert the problematic dates into actual date in my situation? I hope not to have to isolate the "txt" dates, convert them and put them back...

Thanks in advance.

 

[Alex Blakenburg]

It looks like you are pulling the data down from a site using the date format dd/mm/yyyy but your regional setting are mm/dd/yyyy.
Since you are using Power Query, your change type steps or change the first change type step.
What you want to do is righ click on the date field and Change Type > Using Locale (right at the bottom).
Then select a locale that has the date form dd/mm/yyyy (eg UK or Australia will work).

See if that fixes your dates.

Ref: https://www.myonlinetraininghub.com/change-type-using-locale-with-power-query

 

[maounde]

Cool video. Thank you. I tried the entire process. But I still get the same error. I suspect it is because the initial data set has both formats, for some reasons.

I isolated a short sample too work on.
The yellow date shows as General format. The content of the cell is 15-05-2022 16:43:01
The green as a Custom format m/d/yyyy h:mm. The content of the cell is 5/5/2022 12:04:52 PM

I tried to change the format of the yellow to match the green. But it does not change what's displayed... I can't seem to make it so that 15-05-2022 16:43:01 would be recognized as a date. Any thought here?

Also, I don't need to time. I am tempted to remove the time from all cells with a formula, leaving the date. Then work on the problematic 15-05-2022 and turn it into 05/15/2022.

 

[Alex Blakenburg]

The issue isn't just with the dates with the day >=13, I think you will find that if you check back to the source, your other dates will have had their month and day reversed.
Ideally you want to address the whole process ie how are you getting the csv files into Excel ?
If you use Power Query to load the CSV files you can use Change the date using Locale to fix the date.

If you want to do a quick convert you can use Text to Columns.
In this case Fixed Width will work and if you remove the last line you will only get 2 columns.
You can also use space delimiter in which case you will get 3 columns
Set the date format to the source formatting which is DMY.
Select each of the other columns and select Skip.

 

[maounde]

Thanks for this method. It does work.
I download the CSVs from an analytics platform over which I have no control. Unclear how I can get them to fix this problem...