Date problem in excel

[jasonsas]

Hi,

A user regularly imports text files from different pieces of software into Excel. She has complained that many times, the date gets switched around. We're in Australia, so her regional settings are setup as DD/MM/YYYY.

She showed me one particular file that has the date in the right order in the text file, but when she goes to import, SOME of the rows switch to MM/DD/YYYY and others stay the correct way. They only all come across correctly if she goes one more step through the import process and specifically sets DMY for the Date column.

She also said that sometimes she will setup a file from scratch, entering the dates as DD/MM/YYYY and when she closes it and opens it again, the dates are switched!!

Is there anywhere in Excel to ALWAYS set the date as DMY, or dd/mm/yyyy?

Thanks for any help!

 

[xlbo]

not when importing files. My guess is that the files where the dates are turned round are .csv files and the others are .txt files. This is because csv files automatically import because it is a known delimeter (the comma). TXT files do not have a known delimeter and therefore you go through the import process where you set the date type. You could automate this with VBA but other than that, there is no way of changing this behaviour.