VBA to change text to date

[j4ymf]

Hello All

I was wondering is there a way to change text to a date

If I have in cell A1- 01 Jun 20 can that be changed to 01/06/2020
A2 - 02 Jun 20 to 02/06/2020 and so on etc And then replace the hole year as a macros change all the dates in column A

Thank you

 

[JonRowland]

Am I missing something but why use VBA when you can format the column to dd/mm/yyyy?

 

[Joe4]

Am I missing something but why use VBA when you can format the column to dd/mm/yyyy?

If the dates are truly entered as Text, changing the format of the cell will have no impact on it, as Formatting only applies to numeric entries (values and dates) and not text ones.
However, it is rather easy to convert them to valid dates using the "DATEVALUE" function, i.e.
=DATEVALUE(A1)
Then, you can apply any date format you want, since it has been converted to valid date.

If you wanted VBA to do this, one way would be to turn on the Macro Recorder, and record yourself inserting a function and applying this formula and formatting, and copy down for all rows.
You can also hard-code it by Copy/Paste Special Values over top of itself, and deleting the original data.
That will give you most of the code you need, but we can help you make it dynamic (i.e. finding the last row for you), if you need help with that part.
Just post the code here, and we can help you fix it up.

 

[Dave Patton]

Try the following and custom format the date to your preference

T202007a.xlsm
	A	B
1	01 June 2020	01-Jun-20
2	02 June 2020	2-Jun-20
3	03 June 2020	03-06-2020
4
1c

Cell Formulas
Range	Formula
B1:B3	B1	=--TEXT(A1,"dd mmm yyyy")

 

[Dave Patton]

The DateValue certainly works; I just provided the others as extra information.

N.B. The example prepared with Excel 2010 and system regional settings dd-mm-yy
Custom format to your preference d-mmm-yy is unambiguous

T202007a.xlsm
	A	B	C	D
1	01 June 2020	01-Jun-20	01-Jun-20	01-Jun-20
2	02 June 2020	2-Jun-20	2-Jun-20	2-Jun-20
3	03 June 2020	03/06/2020	03/06/2020	03/06/2020
4
1c

Cell Formulas
Range	Formula
B1:B3	B1	=--TEXT(A1,"dd mmm yyyy")
C1	C1	=--A1
D1:D3	D1	=DATEVALUE(A1)
C2:C3	C2	=--(A2)

 

[DanteAmor]

If you want the macro to put the result in the same column, then try the following, you will probably have to update the format of the column to Date.

Sub textTodate()
  With Range("A1", Range("A" & Rows.Count).End(3))
    .Value = Evaluate("=IF({1}," & .Address & "+0)")
  End With
End Sub

 

[Dave Patton]

Hello Dante Amor

I am not very VBA literate.

Please educate me a little. is the 3 a shortcut for "Up" in End(3)?
and the logic of "=IF({1},"

N.B.
If the file is not macro enabled, the user could do the same thing as the macro with Paste Special ADD
copy a blank cell with Control C
select the range of Text that appear to be dates like 01 June 2020
Paste Paste Special Add
Format the numbers to your preference such as 1-Jun-20

 

[DanteAmor]

is the 3 a shortcut for "Up" in End(3)?

Yes it is

the logic of "=IF({1},"

check these answers

Evaluate in VBA & If({1},

Hello all, I am starting to use Evaluate in my VBA codes but looks like since I have office 365 & dynamic arrays, it does something for me that makes it work but it doesn't work for older office versions. I have seen some members here use If({1}, to overcome this issue I guess so is this a...

www.mrexcel.com

copy a blank cell with Control C
select the range of Text that appear to be dates like 01 June 2020
Paste Paste Special Add

it is an option without macro and without formula.

 

[Dave Patton]

to Dante Amor

Thanks for the information. Dave

 

[DanteAmor]

You are welcome