A column has a list of dates, some as [Date] Format and others as [General]

[RockandGrohl]

I'm looking for a VBA way to convert these troublesome dates.

I have a range of dates from January to December, however there are two rogue dates placed in that reoccur a couple hundred times each.

These dates are 31/01/2019 and 28/02/2019 - these are mixed in with actual date-formatted cells as 31/02/2019 and 28/02/2019 - on the surface they look identical however they aren't counted the same way in Excel.


I wrote a macro that selected the whole column, converted the whole thing to date, but this didn't change anything. The cell format changed but the contents were very much still text. So I then added a feature onto the macro that was something like:

Cells(activecell.row, "G").value = cells(activecell.row, "G").value

This worked, but it took a really very long time.

I'm having trouble with generic VBA codes that convert text to date because some dates actually are formatted as a date.


Any help? Thanks.

 

[Joe4]

You should be able to fix that column with a simple Text to Columns. Just go to Step 3 and choose the Date option of DMY (be sure NOT to actually delimit any of your data).
That should convert those Text entries to dates, and leave your other date values as-is.

If you turn on your Macro Recorder. and record yourself performing the Text to Columns, you will have VBA code that will do what you want.

 

[RockandGrohl]

You should be able to fix that column with a simple Text to Columns. Just go to Step 3 and choose the Date option of DMY (be sure NOT to actually delimit any of your data).
That should convert those Text entries to dates, and leave your other date values as-is.

If you turn on your Macro Recorder. and record yourself performing the Text to Columns, you will have VBA code that will do what you want.

Worked perfectly, you're a star!