CSV file dd/mm/yy format

[Shevy]

I wrote a macro which takes an xls file and creates a csv file.
everything is working fine. one problem i am having the date needs to be in dd/mm/yy format. i translate it to that format i even msgbox the value and it is correct. However when i display it in excel in the csv file it comes out as d/mm/yyyy format. I opened the csv in a text editor and it shows the date correctly. why is excel messing around with the date???
any help with this would be appreciated.
THANKS.

[Muddy]

Originally posted by Shevy
I wrote a macro which takes an xls file and creates a csv file.
everything is working fine. one problem i am having the date needs to be in dd/mm/yy format. i translate it to that format i even msgbox the value and it is correct. However when i display it in excel in the csv file it comes out as d/mm/yyyy format. I opened the csv in a text editor and it shows the date correctly. why is excel messing around with the date???
any help with this would be appreciated.
THANKS.

Very odd indeed. I just duplicated what you described. This has to be a bug in Excel ... although it displays the date wrong it doesnt change it when you save it as a csv from excel. I'd call it a bug ... after all excel shouldnt assume 5/5 from a csv file is a date ... it could mean something entirely different (5 of 5, 5 divided by 5, etc).
Sorry I dont have a workaround ... looks like if you are going to use excel to view csv files that you are stuck with this ...

[writelearner]

maybe you could write an autostart macro which change the format of the cell each timeyou open the file

just use the autorecording and right click on your cell and click format cell