Changing date format with VBA

[Adar123]

Hi all,

I have encountered an issue where the Excel not recognizing the dates as dates, I tried to .NumberFormat = "dd/m/yyyy;@" for the whole range but this does not do the job.

If I was to do it manually, a solution is to edit the cell and click enter.

Any thoughts on the solution? Thank you

Book3
	A	B
1	Now	Need
2	14/9/2020	14/9/2020
3	14/12/2020	14/12/2020
4	14/12/2020	14/12/2020
5	14/12/2020	14/12/2020
6	03/12/2021	03/12/2021
7	03/12/2021	03/12/2021
Sheet1

 

[MARK858]

Try selecting the column, clicking Data - Text to Column - Click Next - make sure the "Other" box isn't checked - Click Next and go to the date section and select DMY and click Finish.

You can record the above if you want it in VBA.

You could also try the below if there are no formulas in the column.

Sub Test()
Columns(1).Value = Columns(1).Value
End Sub

 

[Adar123]

Columns(1).Value = Columns(1).Value

I tried this as well, also copied in another cell but makes not difference.

Selecting a column as a manual operation would not work for me: I am producing several sheets multiple times a day.

 

[MARK858]

Selecting a column as a manual operation would not work for me: I am producing several sheets multiple times a day.

As I stated you can record yourself doing the Text to Columns to get the code.

 

[Adar123]

My apologies, I misread your earlier email.

Yes, "Data - Text to Column - Click Next - make sure the "Other" box isn't checked - Click Next and go to the date section and select DMY and click Finish" solution worked and I recorded the macro to get the code.

Thank you!

 

[MARK858]

You're welcome