find and Replace messes the date

[Shrikant]

Hi all !
I use SAP to pull out daily sales sold to different parties.
Now, SAP gives date in dd.mm.yyyy format eg. 31.05.2011
This format is not useful in pivoting or filtering because it is not recognized as dates. I tried to change custom format to dates also but it also doesn't work.

Then I find and replace "." with "/" and it automatically convert into date format. Now I can group dates to week, month etc in a pivot and filter in column head also shows it in group (excel 2007)
problem is when i tried to record macro, the find and replace works fine.
but at the time i re-run the macro it messes all the dates. some dates are displayed as dd-mm-yyyy and some are displayed as mm-dd-yyyy (which are wrong)

Anybody with an explanation?

 

[VoG]

Try selecting the dates, Data > Text to Columns, tick Delimited, click Next twice, tick Date, select DMY then click Finish.

You could record a macro whilst doing that.

 

[Shrikant]

It works normally.
but with macros it is still a problem.

 

[Shrikant]

It works normally.
but with macros it is still a problem.

Hey Sorry Guys,

Tried text to column method again and recorded macro.

It works well.

It opened new possibilities to my work.