VBA Code To Convert DatesFeb 15, 2010
I received a solution to a question recently whereby I was instructed to use the text-to columns function to solve a problem I was having with a column of dates.
Convert Dates To Another Format
The solution provided was;
Select Data > Text to Columns > Next > Next > Column Data Format > Date.
The solution works fine when performed manually but if you perform this function while recording a macro, the crucial piece that changes the date output is not captured by the recorder so when you run the macro nothing happens.
To expand on how I recorded it, after starting the text to column dialogue and pressing Next twice, the dialog provides me with a drop down selector where I am able to change the date format from the default DMY to the desired MDY which - when selected manually - fixes my problem. However, when I record a macro while performing this action the date format choice I made is not being recorded in the macro output so when I try running the macro later on the original data, nothing happens.
The recorded macro ends up looking like this; ....