Date Format Issue

I have a spreadsheet with a column of dates but they're in 2 different formats. Some dates read as "30 Mar" (for March 30th) and are formatted as "General" type. Other dates read 3/30/2017, formatted as actual dates. I'm trying to use VBA to go down the column and make them all the same. For purposes of what I'm going, I'm trying to get them all to read like the "30 Mar" example, despite the other one seeming more logical.

That said, I've put together the following code but cannot get the desired result to return in the spreadsheet's cell. I put in a MsgBox line of code just to make sure I'm getting the value I want, and that's working. The Message Box reads "30 Mar" as I want, but when it writes it to the spreadsheet I get the numeric date value "42824" no matter what format I assign to the cell.

Am I doing something wrong? It would seem my code works based on the Message Box output, so I'm thinking it's something to do with the quirkiness of Excel's dates/formats, but I feel it should be working. Any help or insight is great appreciated!

Please Login or Register  to view this content.

Try formatting the cell(s) before you put the value(s) in them.

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

Does this do what you want?

Please Login or Register  to view this content.