VBA Formatting

[collett09]

Can some one please help i am new to VBA but can read some code i am trying to format a cell from 20100730 to 2010-07-30 in a macro i have a sheet with about 14000 dates on that will need to be changed to this format in a text value to be uploaded in to a database.

[Paul]

Hi Collett, welcome to the forum.

In your current data, are the dates actual dates or are they text that look like dates? You said they're currently YYYYMMDD - so is that a custom format of a real date, or just text?

[collett09]

they are just text format it is just a dump out of a old database.

[Paul]

The quickest way would be to use a formula. Assuming your current values are in A1:A14000, then in B1 insert the formula:

=LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,2)

Fill that down to B14000. Copy B1:B14000, select A1 and use PasteSpecial -> Values. Delete column B.

If you really need to use a macro, use:

Please Login or Register  to view this content.

[ChemistB]

Here's a VBA solution

Please Login or Register  to view this content.

Does that work for you?

[Paul]

Chemist, I don't think just formatting the date is the key here, but I may be wrong. If the end result is to be able to import the column into a database that is expecting "date" values in yyyy-mm-dd format, Excel has to actually contain data stored as yyyy-mm-dd, not just dates formatted to show that way.

I could be wrong.. only time and collett can say in this instance.

[ChemistB]

Ahhh, I read it as the text dates were coming from a data base, not going to... Nevermind.

[collett09]

Thank you all for the replays i will test and let you know if it works

[collett09]

Thank you Paul works great you have been a great help and i know where to come for help from now on