Convert string dates to proper numerical date format

Hello, Everyone!

I am trying to convert text 'dates' like Sat. 1/05/2007 to real dates. I need to find all 'date' cells in range B4:B100 (they are filled in yellow), strip off the offending text (i.e. Sat.), and use DateValue to turn the rest into a proper, numerical date in the ddd mm/dd/yy format, arial, bold, 10pt, and centered in place.

I've spent days trying to write this macro. I've had help from experts in forums, but whenever I need to make any slight modification, a
trainwreck ensues. Please see the code below.

Please Login or Register  to view this content.

This doesn't even begin to work and I can't begin to guess why. Could
somebody out there please make the tweaks and tell me what I did wrong, that I might not do it hence?

I thank you greatly, and have a terrific day.

Arlen

Hi Arien

I have altered your code and provided some comments (you should be able to compare the code below with your code to see where you went wrong):

Please Login or Register  to view this content.

Cheers
Shaun

1. The rules request that you wrap your code...
2. (a) The variable MyDate is declared as string-type; but in line #5 you wrote: MyDate.Value, which should have given you a compile error because strings have no .Value qualifier.
(b) Line #5 is trying to write the contents of the variable to the cell:

(Let the) cell.Value = MyDate.Value

3. Since your For..Each loop is testing the formatting of a cell, line #5 is meant to load the variable MyDate with the contents of the cell. It should be:

Please Login or Register  to view this content.

Note: MyDate is empty (you did not initialize it prior to line #5) so that the code:

cell.Value = MyDate

would have erased the contents of the test cell!

Originally Posted by Codpops

Shaun and Proton,

The code is perfect. You're both awesome for taking the time to explain this. This is the only way I can really learn VBA at all.

Now, I've rounded up this final bit of code to format the dates after converting so that they look like they did as text...Arial, 10, bold and centered. Do I use cell As Range, or create a new Range, r? And where does it all go...

Please Login or Register  to view this content.

And, truly, that is all.

Thanks guys.

You [Offensive comment removed by Forum Administrator].
That's part of my code in your CROSS POST (MrExcel)

Codpops,

While we don't condone cross-posting, we understand users are going to do it. Please review the rules, and if you plan to cross-post in the future be sure to post a link to the other post.

Also, if you get a resolution to your problem on the other site be sure to include that in this forum.

Thank you,
Paul