Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates using Regional Options
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates using Regional Options
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates using Regional Options
Dave Peterson wrote: 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 wrote: 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 Thanks for the helpful advice Dave By experimentation, I've been able to get my p[roblems resolved! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using VBA to temporarily bypass regional options | Excel Programming | |||
Regional options support for currency | Excel Programming | |||
Regional options support question | Excel Programming | |||
Problems with Excel Horizontal arrays with regional options using. | Excel Discussion (Misc queries) | |||
Regional Options and Decimal symbol from VBA | Excel Programming |