Change M(M)/D(D)/YYYY format = General ...

[adambc]

... to D(D)/M(M)/YYYY format = Date (so that I can sort old to new) ...

I am working with two extract files from another system that I cannot influence!

I am reading them into a WS in my WB using Power Query / Append ...

But in what should be date columns, the values (in the extracts, PQ is not changing them) are eg 7/1/2023 & 10/13/2022 and format = General (PQ is seeing them as Text) - whereas I need them to read 1/7/2023 & 13/10/2022 respectively AND format = Date ...

Can someone help me please/

Thanks ...

 

[Candyman8019]

You can create another column and use a formula like this:

=TEXT(A1,"dd/mm/yyyy")

then format that column as date.

 

[MARK858]

In Excel

Select the column
Data tab
Text to Columns
Delimited
Next
Make sure all the boxes are unchecked
Next
Select Date checkbox
Change the option to MDY (yes MDY as you want the source format)
Click Finish

Important Edit: removed the PowerQuery suggestion as doesn't work correctly

 

[Rick Rothstein]

**Important** - Are any of these coming in as real dates (aligned right) while others are coming in as text (aligned left)?

 

[adambc]

In Excel

Select the column
Data tab
Text to Columns
Delimited
Next
Make sure all the boxes are unchecked
Next
Select Date checkbox
Change the option to MDY (yes MDY as you want the source format)
Click Finish

Important Edit: removed the PowerQuery suggestion as doesn't work correctly

Bingo, it was the MDY that was foxing me - thanks ...

Shame I can't do it in Power Query!

 

[adambc]

**Important** - Are any of these coming in as real dates (aligned right) while others are coming in as text (aligned left)?

No - now solved by the post above - but thanks for responding ...

 

[MARK858]

**Important** - Are any of these coming in as real dates (aligned right) while others are coming in as text (aligned left)?

It doesn't make any difference when using text to columns, it is how it appears in the cell, not it's underlying value
So as long as they are all US dates coming in on a UK setup computer (A3,A6 and A7 were right aligned, the rest left aligned)

Book1.xlsb
	A
1	Date
2	7/23/2023
3	07/01/2023
4	9/16/2023
5	09/16/2023
6	11/12/2023
7	09/10/2023
Sheet5

becomes

Book1.xlsb
	A
1	Date
2	23/07/2023
3	01/07/2023
4	16/09/2023
5	16/09/2023
6	12/11/2023
7	10/09/2023
Sheet5

 

[MARK858]

Bingo, it was the MDY that was foxing me - thanks ...

You're welcome

 

[Rick Rothstein]

No - now solved by the post above - but thanks for responding ...

It is good that your answer is "No", otherwise the solution would have left you with some inaccurate dates.

 

[MARK858]

It is good that your answer is "No", otherwise the solution would have left you with some inaccurate dates.

Rick, please see my last post (number 7) and let me know which results you consider wrong, if it is imported from a text file that is how the first sample would appear on a uk machine (and would be right aligned (real dates) if they looked like uk dates in the text file)