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 ...

 

[Rick Rothstein]

Rick, please see my last post (number 7) and let me know which results you consider wrong

I didn't see that message (I did not get a notification that it was posted). Untested, but it is my understanding that if a UK text date of, say, 9/3/2023 (UK date of March 9, 2023) came in, Excel would change "accept" it as written and place a real date of 9/3/2023 (US date of September 9, 2023) into the cell. Are you saying Text To Columns only sees the 9/3/2023 as text even though that is only a cell format applied to what Excel thinks is a valid date? What if I apply a cell format of d/m/y to a real September 9, 2023 date... Text To Columns would see that as March 9th because that is what is in the cell?

 

[MARK858]

Untested, but it is my understanding that if a UK text date of, say, 9/3/2023 (UK date of March 9, 2023) came in, Excel would change "accept" it as written and place a real date of 9/3/2023 (US date of September 9, 2023) into the cell.

No, Text to Columns reverses both what it sees as a text date and the real date.

Book1.xlsb
	A	B	C
1	Date
2	7/23/2023
3	07/01/2023		07 January 2023
4	9/16/2023
5	09/16/2023
6	11/12/2023		11 December 2023
7	09/10/2023		09 October 2023
Sheet6

Cell Formulas
Range	Formula
C3,C6:C7	C3	=A3

Book1.xlsb
	A	B	C
1	Date
2	23/07/2023
3	01/07/2023		01 July 2023
4	16/09/2023
5	16/09/2023
6	12/11/2023		12 November 2023
7	10/09/2023		10 September 2023
Sheet6

Cell Formulas
Range	Formula
C3,C6:C7	C3	=A3

 

[MARK858]

@Rick Rothstein probably a better example if bringing in from a text file

Book1.xlsb
	A	B	C
1	Date
2	01/10/2023		01 October 2023
3	01/11/2023		01 November 2023
4	01/12/2023		01 December 2023
5	01/13/2023		01/13/2023
6	01/14/2023		01/14/2023
Sheet8

Cell Formulas
Range	Formula
C2:C6	C2	=A2

Book1.xlsb
	A	B	C
1	Date
2	10/01/2023		10 January 2023
3	11/01/2023		11 January 2023
4	12/01/2023		12 January 2023
5	13/01/2023		13 January 2023
6	14/01/2023		14 January 2023
Sheet8

Cell Formulas
Range	Formula
C2:C6	C2	=A2

 

[Rick Rothstein]

@MARK858 - Again, I may be wrong because I have never had to work with international dates, but this is my understanding. If these were the text dates coming in from England to a US computer...

21/3/2023
9/3/2023

then when they got placed into Excel, this is what would be in the cells...

21/3/2023 (text date - left justified)
9/3/2023 (real date - September 3rd, not March 9th - right justified)

The 9/3/2023 would just be a format applied to a real date that is not the intended date... that format could just as easily have been dmmmyyyy or d/m/yyyy (still not March 3rd even though the format would display it that way). My concern is that Text To Columns would use the "real" date that Excel placed in the cell when the cell contained a real date and the text date when it was not a valid date in the US locale.

 

[MARK858]

If I put in my cells (US dates)
3/21/2023
3/9/2023

Book1.xlsb
	A	B	C	D
1	Date
2	3/21/2023		FALSE	3/21/2023
3	03/09/2023		TRUE	03 September 2023
Sheet9

Cell Formulas
Range	Formula
C2:C3	C2	=ISNUMBER(A2)
D2:D3	D2	=A2

I get after Text to Columns

Book1.xlsb
	A	B	C	D
1	Date
2	21/03/2023		TRUE	21 March 2023
3	09/03/2023		TRUE	09 March 2023
Sheet9

Cell Formulas
Range	Formula
C2:C3	C2	=ISNUMBER(A2)
D2:D3	D2	=A2

 

[Rick Rothstein]

@MARK858 - Okay, I just did a test and it looks like Text To Columns is not fooled by the cell value, so my concern does not apply. It even worked correctly if I applied a custom format of d/m/yyyy to the date (which is September 3rd in the cell)... it became a real date of March 9th BUT because of the custom format applied to the cell, it displayed it as 9/3/2023, so I guess that is something to be aware of.

 

[MARK858]

Yes I got similar results (didn't try the custom format as a US date though), see post 15

 

[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

Follow up question ...

Is there anyway to stop a Power Query refresh resetting the format - I need to be able to sort by date in the PQ table?

Can't see anything in the PQ Properties!

Thanks ...

 

[MARK858]

Not that I can think of (then again I've never needed to do it), can't you just record yourself carrying out the Text to Columns so you have it in a macro (if you are sorting after doing the query)?

 

[adambc]

Not that I can think of (then again I've never needed to do it), can't you just record yourself carrying out the Text to Columns so you have it in a macro (if you are sorting after doing the query)?

Which is what I did and created this module ...

Sub RefreshAllAndTextToDate()

ActiveWorkbook.RefreshAll

Columns("E:E").Select
    Selection.TextToColumns Destination:=Range( _
        "TicketsCombined[[#Headers],[Date Created]]"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True

Columns("F:F").Select
    Selection.TextToColumns Destination:=Range( _
        "TicketsCombined[[#Headers],[Date Created]]"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True

End Sub

But ... if I run it as is, the only piece that runs is the RefreshAll ie the TextToColumns part of the code doesn't run- if I comment out the RefreshAll after running it, only the Columns("E:E").Select runs ie it doesn't then run the Columns("F:F").Select piece of the VBA - what am I missing?!!!