Date Change

[vmjan02]

Not sure as it is not converting the date,
the data is there, the code is not convert mm/dd/yyyy to dd-mm-yyyy

the problem is if till 12 day of any month the date come as mm/dd/yyyy and as it 13 day of any month then the input file comes as dd-mm-yyyy

so need a code that will fix the to dd-mm-yyyy always.

Sub datachange()

Application.Wait (Now + TimeValue("0:00:02"))
  Range("B2:B80000").TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=False
  Range("B2:B80000").NumberFormat = "dd-mm-yyyy"

End Sub

Book3
	B
1	CREADTED_DATE
2	2/11/2023
3	31-10-2023
4	31-10-2023
5	31-10-2023
6	31-10-2023
7	1/11/2023
8	1/11/2023
9	1/11/2023
10	1/11/2023
11	1/11/2023
12	1/11/2023
13	2/11/2023
14	31-10-2023
15	31-10-2023
16	31-10-2023
17	31-10-2023
18	31-10-2023
19	31-10-2023
20	31-10-2023
21	31-10-2023
22	31-10-2023
23	1/11/2023
Sheet1

 

[6StringJazzer]

Is the data you showed here the values before you run your code? Or after?

 

[vmjan02]

Is the data you showed here the values before you run your code? Or after?

before , actual input file

 

[vmjan02]

Hi
any udpate on it

 

[MARK858]

Not sure about your question, what you have posted is all in day, month, year format

 

[6StringJazzer]

The problem is not in the format you are applying, it is in your data.

Your initial data is ambiguous. Do you expect 2/11/2023 to be interpreted as Nov 2 or Feb 11?

What are your localization settings in Excel/Windows for default date format?

 

[vmjan02]

Do you expect 2/11/2023 to be interpreted as Nov 2 or Feb 11?

the system setting is in us formate, it should be 2 nov, so need a right fix

 

[vmjan02]

Not sure about your question, what you have posted is all in day, month, year format

yes all is in dd mm yyyy but as the date cahnges to 13 the input data comes as 13-11-2023 its 13 Nov and till 12 day its input comes in as 12/11/2023 its 12 Nov,

so need to modifity all the dates in dd-mm-yyyy formate, if the input file is in dd/mm/yyy or dd-mm-yyyy final output should be in dd-mm-yyyy only.

 

[MARK858]

If you run the code below on the original data, can you post the result please?

Sub datachange()
Range("B2:B80000").TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=False
 Range("B2:B80000").NumberFormat = "General"

End Sub

Can you also select and copy the original data and paste it directly in the thread please (without using XL2BB)

 

[vmjan02]

a

Sub datachange() Range("B2:B80000").TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(1, 4), TrailingMinusNumbers:=False Range("B2:B80000").NumberFormat = "General"

after running this the result, and orignial data i have already share on my 1 st post in xl2bb

WEK	CREADTED_DATE
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​
44​	45230​