Default American-format date reading when opening Excel files

Question

Is there a way to change the default American-format date reading when opening Excel files? For example, 07/30/09 in the first line is interpreted automatically as July 30, 2009 and D/M/Y is maintained for the rest of the column. However, 03/08/09 is read as March 8, 2009, and M/D/Y is used for the rest of the column, even though the date is August 3. Any simple suggestions?

pgstats · Answer

I'm sorry, I gave you a SAS answer to a JMP question. In fact, it's more a Microsoft Office question, because that's where the problem started. It's happened to me when importing old worksheets. If you bring into your localized version of MS-Office some text data with a different date convention, then it either remains as text or is transformed into the wrong date, with both situations occuring in the same column. When you inherit a messed-up file like that, you're stuck. Manual work is the only remedy.

PG

mahatmagrande · Answer

Thanks, PGStats, but it's more complicated than that, and I think I'm stuck with manually correcting the dates and forever trying to understand what's gone wrong when importing data from all the various dataloggers and students I have. The problem lies in both Excel (business software!) and JMP. My computer is set to read and express dates as "dd/mm/yyyy" (Canadian English). Therefore mm/dd/year dates are not possible in Excel - it turns them to text. But JMP doesn't help. Even when it imports the dd/mm/yyyy data correctly, it expresses the date as mm/dd/yyyy by default, and if I want it any different, it appears I have to go in and change it back.

Sigh.

pgstats · Answer

Try setting the proper LOCALE= system option for your country. Or if you just want to change the date interpretation and nothing else, set the DATESTYLE= option. Check the doc for National Language Support.

If those are you own Excel files, convert the dates expressed as text into proper Microsoft Office dates (which are numbers). Then there would be no ambiguity when importing them.

PG