Dates using Regional Options

[AussieDave]

I have two groups of users entering data on identical spreadsheets -
one in Asia (using Regional Option of yyyy/mm/dd) and the other in
Australia (using dd/mm/yyyy). Both automatically load data into their
respective spreadsheets from the same csv file which contains dates in
dd/mm/yyyy format. My problem is how to manipulate dates on the
spreadsheet so that I end up with the same output format on both.
TIA, Dave

 

[Dave Peterson]

Maybe you could do some experimentation.

Change your windows regional settings to various orders (mdy, ymd, dym...) and
then save some test CSV files.

Then change your windows regional settings to the ymd (then dmy) and try
importing the CSV data.

If you get the dates that you want (be careful--test by using an unambiguous
date format in excel (mmmm dd, yyyy).

If you get what you want with both settings, then use that.

I think I'd be prepared to give them a text (.txt) file and give them
instructions on how to use import text wizard (data|text to columns).

Another thing you may want to do is reformat the date field as General -- so
it's just a number. Then share that CSV file and tell them to import the data,
but format the dates the way they want to see them -- after the import.

===
When I do this (and the layout of the text file doesn't change, I save the data
as a .Txt file, but give them a workbook that prompts them for the input file
and parses the data correctly.

It'll even add page layout stuff, headers, filters, ...

 

[AussieDave]

Maybe you could do some experimentation.

Change your windows regional settings to various orders (mdy, ymd, dym...) and
then save some test CSV files.

Then change your windows regional settings to the ymd (then dmy) and try
importing the CSV data.

If you get the dates that you want (be careful--test by using an unambiguous
date format in excel (mmmm dd, yyyy).

If you get what you want with both settings, then use that.

I think I'd be prepared to give them a text (.txt) file and give them
instructions on how to use import text wizard (data|text to columns).

Another thing you may want to do is reformat the date field as General -- so
it's just a number. Then share that CSV file and tell them to import the data,
but format the dates the way they want to see them -- after the import.

===
When I do this (and the layout of the text file doesn't change, I save the data
as a .Txt file, but give them a workbook that prompts them for the input file
and parses the data correctly.

It'll even add page layout stuff, headers, filters, ...

Thanks for the helpful advice Dave
By experimentation, I've been able to get my p[roblems resolved!