change date format - recalculate cell value

Hi,

having an annoying problem here.
I need to change the date format of a report which is imported to my excel sheet since i later need to compare the date values with another report. anyway, currently the date format is dd-MMM-yy (eg. 21-Nov-11) but via search and replace I replace the text for the month with the respective number to dd-mm-yyyy (eg. 21-11-2011). This code is doing that for me.

Please Login or Register  to view this content.

however, now I am receiving one error after another from excel saying that it cannot calculate the cell value of 13-08-2011 for example. If i disable the cell value recalculation and merely change the format of the column, it works most the time correclty, but sometimes ends up mixing me the order of the date (eg. 12-07-2011 becomes 7-12-2011 (M-DD-YYYY)).

Can anyone maybe advise me an easier approach towards this problem?
Thanks a lot!
A2k

Why are you doing this at all? Excel does not store dates like that, it just holds a date number and what you see is the presentation of the data. Thus you just need to set a different date format.

Originally Posted by Bob Phillips

Why are you doing this at all? Excel does not store dates like that, it just holds a date number and what you see is the presentation of the data. Thus you just need to set a different date format.

because my imported report is in delimited format which is again storing everything as a text format by default.
the other issue is that the other report to which I need to compare the dates is as well stored as text but the date format is different.
By merely "changing" the format i am not getting anywhere since I still have to recalculate the cell value in order to make excel see this as a date and then apply the display format. with that in mind, I already am doing it the easy way as you see from my macro, but I run into the problem that excel is misinterpreting the date format, therefore having changes such as m-dd-yyy.

Believe me, if I could get rid of this issue once and for all, I would be a much happier man.
Thanks

Difficult to comment without the data. Can you post the workbook?