Need help w/ data conversion

I'm trying to use MT's history data in another program that needs the date in mm/dd/yyyy format. I've tried running a macro in Excel to convert it, but I'm running into all kinds of problems which have to be manually fixed, which of course defeats the whole purpose of the macro.

Any Excel gurus out there, or does anyone know of a conversion utility for MT's history data?

Thanks!

Which format is the date in presently?
Sorry, I use VT for charting mainly.

Bemac,

It comes out of MT as 2006.06.29, which of course really throws Excel for a loop. I've done all kinds of things to replace the dots with slashes and rearrange to mm/dd/yyyy format and I get that done. But the program I'm importing into still rejects it until I reopen the file in Excel and change the format of the date column (which the macro has already done). I'm stumped.

Did you try this.

Formula in Cell F4 Getting Input from Cell D4.

=DATEVALUE(CONCATENATE(MID(D4,FIND(".",D4)+1,2),"/",RIGHT(D4,2),"/",LEFT(D4,FIND(".",D4)-1)))

For anyone else following along, I use this formula to convert Dates in Ecc. Calanders which are occasionally published as text to track and evaluate Reaction Moves to certain News Events.

Bemac,

I don't use the exact formula you have, but something similar gets the correct result. However, the problem seems to be copying the result back into Column A. It copies fine, the column is formatted as "date" and everything looks fine. But the other program still rejects it until I reopen the file and format Column A to "date" once again. When I reopen the file and go to Format Cell, nothing (not even General) is highlighted. So I click on Date, save and everything works fine.

Is there some kind of bug in Excel?