Copying date into Excel

[afc171]

Hi all,

I have to copy data from SAP which puts the date as 01.07.24 but Excel doesn't pick this up properly, even when changing the format. So I ran a macro to convert text to column, but when you run the macro each time it will mess up...

So this is correct, but when you run macro again it swaps the day and month around...

ProductPath.xlsm
	A	B	C	D	E	F
1	PRODUCT	QTY	BATCH	START DATE	START	DOCUMENT
2	103008	1	167530	05/02/2024	14:18:22	11093552
3	1990-0804	2	165544	05/02/2024	14:15:35	11093552
4	204002	2	166637	05/02/2024	14:17:59	11093551
5	402002	11	167542	05/02/2024	14:16:26	11093388
PRODUCTS

Vs..

ProductPath.xlsm
	A	B	C	D	E	F
1	PRODUCT	QTY	BATCH	START DATE	START	DOCUMENT
2	103008	1	167530	02/05/2024	14:18:22	11093552
3	1990-0804	2	165544	02/05/2024	14:15:35	11093552
4	204002	2	166637	02/05/2024	14:17:59	11093551
5	402002	11	167542	02/05/2024	14:16:26	11093388
6	8437-5208	2	152470	02/05/2024	13:59:55	11093553
PRODUCTS

    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("D2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
    Range("Table3[[#Headers],[PRODUCT]]").Select

I just need this to run once or is there another way to sort it?

Thanks

 

[afc171]

Change the Array(1, 2) it seems to be working now!