Find and replace recorded macro messes up the dates

What I need to achieve is to convert the info in column U which contains dates that are saved as text in format xx.xx.xxxx to change them into dates format dd/mm/yyyy (recognised as dates, not as text). You can do find "." and then replace "/" which does the trick. But as soon as I do the same thing with recorded macro it messes up some of the dates as they are still kept as text. It changes the "." with "/" on all cells but some of them are picked up by excel as dates others remain as text. You can really easily tell which one it recognizes as text and which as dates as text is kept aligned left, dates are aligned to right. To make things even more awkward the ones it picks up as date it swaps the dd and mm. If before its 09.03.2011 then after its 03.09.2011. Even when you change the format to dd/mm/yyyy the date remains the same 09.03.2011. If you do the process manually then problems excest. Please help with better solution.

Here is my rubbish recorded macro.

Please Login or Register  to view this content.

I will attach the xls as well.



Some other solution than then recorded macro I have is needed.
Please help

Do you really need a macro for this?

In a spare column in row 2

Please Login or Register  to view this content.

Drag/Fill Down

Copy this column and PasteSpecial > Values to U2
Format Column U as date.

Do the same for any other date column you need to change.

Unfortunately I do need macro for this. I work with lots of reports generated by software called SAP. I need to deal with this report every single day at work so running macros is a good solution to reduce your workload and have bit more time to deal with other important tasks. I already have loads of macros that are doing other tasks and they are depending on this macro. I only now figured out it has a problem. So this particular type of report has this silly date formating. If I would feel happy with manually sorting out the dates in that column U, I would then do highlight the column do find and replace and problem would be sorted. As I said it works when you do it manually. Which is even easier option than suggested. Its when you run the recorded macro it messes up. Don't know why that is happening. Its very silly. Try it. Do it manually, highlight the column u, Ctrl+F find . replace with /, press ok, see the result, undo it and then run the recorded macro.You can see then the difference between doing it manually or running this macro. Its then all messed up. PS! For check purpose col T and col U should have the same dates.


Any ideas that would work as a macro.

Cheers
Rain

Try this

Select any cell in the column you need to convert the strings to dates and run this macro

Please Login or Register  to view this content.

This assumes that all the data in the column is a string formatted as your sample workbook or is blank, and the data begins in row 2

N.B.
Column IV is the last column in Excel 2003 and is used as a helper column in this macro.

Hope this helps

hi, rain4u, try this:

Please Login or Register  to view this content.