Convert US Time to UK

[fapb48]

Hi everyone,

I have a CSV that has US dates on it.

For whatever reason i cannot seem to convert US to UK dates.

I have already tried the Text to Columns feature but no changes at all.

On the format dialog box it shows the UK version of the wrong date.

Example:

Date on my rows are 01/03/2023 as in 3rd of January 2023
On the date format the preview shows 1st of March.

I am guessing that the system already thinks that this is already a UK Date.. Which in fact it is not..

The main problem is that when you want to filter by days it is trying to filter by months instead of what i really need, which is days...

Any suggestions would be much appreciated it.

Thank you

 

[Legacy 143009]

Did you chooıse "Date: MDY" while converting text to columns?

 

[RoryA]

The main problem is that when you want to filter by days it is trying to filter by months instead of what i really need, which is days...

That always happens unless you turn off date grouping in the autofilter menus in the Advanced section of Excel option.

 

[fapb48]

Did you chooıse "Date: MDY" while converting text to columns?

Yes, i did chose MDY and nothing has changed

 

[fapb48]

That always happens unless you turn off date grouping in the autofilter menus in the Advanced section of Excel option.

On a different spreadsheet with UK dates on it filters just fine.
On this CSV i only have January data and as you can see from my screenshot its trying to filter all months of the year.

 

[Legacy 143009]

A very winding method:
1. Convert your column to table.
2. Go to Data tab. Select Form Table/Range.
3. In the Power Query window, delete the 2nd step if exists. (Converted Type or something similar. Mine is in Turkish)

4. Click to "ABC123" in column header. Select "Use local settings" at the very bottom.

5. Select Date and English(U.S).

It should automatically detect the US format and represent the value in your local format. Then you may copy and paste the values somewhere else.

Btw, my value in the actual cell is in plain text format.