Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
different values depending on how text file is opened
Sorry for the lengthy post...
1- explanation With File Explorer double-clicking on a tab-delimited text file is not the same as drag'n'dropping that file into an occurrence of Excel. All dates are changed to another format with double-clicking, they are kept 'as is' with drag'n'drop. With double-clicking : Dates are standard ISO (dd/mm/yyyy ) but Excel *transforms* the actual data into mm/dd/yyyy. The swapping of dd and mm only occurs when dd < 13. Otherwise the field is kept left-aligned like a text filed. day(<myCell) gives different results depending on how the file was opened so it is not a mere formatting problem. The data is altered by Excel and it does not seem right. At least it should always be done consistently. 2- example : Text file : foo.gs A <tab 16/09/2006 B <tab 08/09/2006 Displayed in Excel after double-clicking : A.........B..........DAY(B) A 16/09/2006 16 B 09/08/2006 09 <= wrong value : why swap dd and mm at all ? Displayed in Excel after dragging : A.........B..........DAY(B) A 16/09/2006 16 B 08/09/2006 8 Any way to fix this ? This is on a French Windows XP system, set to French(France) and the formatting for date is dd/mm/yyyy. I can't change this because other apps need this setting. Not sure Excel uses it either! Files have a .gs extension and are associated with Excel in File Explorer options menu. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
different values depending on how text file is opened
Hi,
Excel by default uses format as selected in Windows - Control Panel - Regional and language options. I can namely see from your example that when excel displays date correct it does not display it as date (if the date is aligned left), but as text. Try to change this date options to dd/mm/yyyy and then open this txt file. If dates are displayed correctly, save the file as excel (dates will be saved as dates) and then change date format in control panel. If you change it for example to dd.mm.yyyy, excel should display dates as dd.mm.yyyy. Regards, Nika Lampe "notMe" je napisal: Sorry for the lengthy post... 1- explanation With File Explorer double-clicking on a tab-delimited text file is not the same as drag'n'dropping that file into an occurrence of Excel. All dates are changed to another format with double-clicking, they are kept 'as is' with drag'n'drop. With double-clicking : Dates are standard ISO (dd/mm/yyyy ) but Excel *transforms* the actual data into mm/dd/yyyy. The swapping of dd and mm only occurs when dd < 13. Otherwise the field is kept left-aligned like a text filed. day(<myCell) gives different results depending on how the file was opened so it is not a mere formatting problem. The data is altered by Excel and it does not seem right. At least it should always be done consistently. 2- example : Text file : foo.gs A <tab 16/09/2006 B <tab 08/09/2006 Displayed in Excel after double-clicking : A.........B..........DAY(B) A 16/09/2006 16 B 09/08/2006 09 <= wrong value : why swap dd and mm at all ? Displayed in Excel after dragging : A.........B..........DAY(B) A 16/09/2006 16 B 08/09/2006 8 Any way to fix this ? This is on a French Windows XP system, set to French(France) and the formatting for date is dd/mm/yyyy. I can't change this because other apps need this setting. Not sure Excel uses it either! Files have a .gs extension and are associated with Excel in File Explorer options menu. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
different values depending on how text file is opened
Hi Nika,
Thanks for suggesting the settings for Windows but the Regional and language options are already set to French(France) and date format = dd/mm/yyyy. I have no idea how to fix this behaviour in Excel... Another idea maybe ? Hi, Excel by default uses format as selected in Windows - Control Panel - Regional and language options. I can namely see from your example that when excel displays date correct it does not display it as date (if the date is aligned left), but as text. Try to change this date options to dd/mm/yyyy and then open this txt file. If dates are displayed correctly, save the file as excel (dates will be saved as dates) and then change date format in control panel. If you change it for example to dd.mm.yyyy, excel should display dates as dd.mm.yyyy. Regards, Nika Lampe "notMe" je napisal: Sorry for the lengthy post... 1- explanation With File Explorer double-clicking on a tab-delimited text file is not the same as drag'n'dropping that file into an occurrence of Excel. All dates are changed to another format with double-clicking, they are kept 'as is' with drag'n'drop. With double-clicking : Dates are standard ISO (dd/mm/yyyy ) but Excel *transforms* the actual data into mm/dd/yyyy. The swapping of dd and mm only occurs when dd < 13. Otherwise the field is kept left-aligned like a text filed. day(<myCell) gives different results depending on how the file was opened so it is not a mere formatting problem. The data is altered by Excel and it does not seem right. At least it should always be done consistently. 2- example : Text file : foo.gs A <tab 16/09/2006 B <tab 08/09/2006 Displayed in Excel after double-clicking : A.........B..........DAY(B) A 16/09/2006 16 B 09/08/2006 09 <= wrong value : why swap dd and mm at all ? Displayed in Excel after dragging : A.........B..........DAY(B) A 16/09/2006 16 B 08/09/2006 8 Any way to fix this ? This is on a French Windows XP system, set to French(France) and the formatting for date is dd/mm/yyyy. I can't change this because other apps need this setting. Not sure Excel uses it either! Files have a .gs extension and are associated with Excel in File Explorer options menu. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
different values depending on how text file is opened
How about just dropping the doubleclicking and the drag/drop and do a File|Open.
Then you'll see a text import wizard appear when you open your .txt file. You can specify the order of the date fields the way you need. notMe wrote: Hi Nika, Thanks for suggesting the settings for Windows but the Regional and language options are already set to French(France) and date format = dd/mm/yyyy. I have no idea how to fix this behaviour in Excel... Another idea maybe ? Hi, Excel by default uses format as selected in Windows - Control Panel - Regional and language options. I can namely see from your example that when excel displays date correct it does not display it as date (if the date is aligned left), but as text. Try to change this date options to dd/mm/yyyy and then open this txt file. If dates are displayed correctly, save the file as excel (dates will be saved as dates) and then change date format in control panel. If you change it for example to dd.mm.yyyy, excel should display dates as dd.mm.yyyy. Regards, Nika Lampe "notMe" je napisal: Sorry for the lengthy post... 1- explanation With File Explorer double-clicking on a tab-delimited text file is not the same as drag'n'dropping that file into an occurrence of Excel. All dates are changed to another format with double-clicking, they are kept 'as is' with drag'n'drop. With double-clicking : Dates are standard ISO (dd/mm/yyyy ) but Excel *transforms* the actual data into mm/dd/yyyy. The swapping of dd and mm only occurs when dd < 13. Otherwise the field is kept left-aligned like a text filed. day(<myCell) gives different results depending on how the file was opened so it is not a mere formatting problem. The data is altered by Excel and it does not seem right. At least it should always be done consistently. 2- example : Text file : foo.gs A <tab 16/09/2006 B <tab 08/09/2006 Displayed in Excel after double-clicking : A.........B..........DAY(B) A 16/09/2006 16 B 09/08/2006 09 <= wrong value : why swap dd and mm at all ? Displayed in Excel after dragging : A.........B..........DAY(B) A 16/09/2006 16 B 08/09/2006 8 Any way to fix this ? This is on a French Windows XP system, set to French(France) and the formatting for date is dd/mm/yyyy. I can't change this because other apps need this setting. Not sure Excel uses it either! Files have a .gs extension and are associated with Excel in File Explorer options menu. -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
different values depending on how text file is opened
Just the one I suggested.
notMe wrote: Hi Dave, Thanks for telling me about the assistant etc. I know it well but I have just too many files to deal with and also for drag'n'drop I can't trust my users to not double-click on files (often sent by email anyway so they'd need to save first etc.). I can't believe Excel does not work well if you double-click on text file that contain dates... So I would like Excel to behave consistently regardless of the opening mode. I mean, I've never heard of a database that *transforms* the data depending on how you open a file... Why does drag'n'drop check my system's settings and double-click would'nt ? I wish all our business partners sent us files in a more reliable form but we can't ask them to change their ways for us. It is so bad that I recently started writing SQL code to download records from our mainframe with, instead of the classic '01/09/2006' dates, something like '=date(2006, 09, 01)' and then apply a macro to make the formulas effective in each cell under Excel. Looks so bizarre I know there must be a simple yet effective solution out there... drag'n'drop does it, maybe something in file types association ? some DDE wizardry ? I guess I could invest in a VB thing that would drag'n'drop for me if I double-click... I know there are workarounds but one day or another someone will make wrong assumptions on our business because their file was not "opened the right way". I would rather find a definitive solution before this happens, really. Any idea ? "Dave Peterson" a écrit dans le message de news: ... How about just dropping the doubleclicking and the drag/drop and do a File|Open. Then you'll see a text import wizard appear when you open your .txt file. You can specify the order of the date fields the way you need. notMe wrote: Hi Nika, Thanks for suggesting the settings for Windows but the Regional and language options are already set to French(France) and date format = dd/mm/yyyy. I have no idea how to fix this behaviour in Excel... Another idea maybe ? Hi, Excel by default uses format as selected in Windows - Control Panel - Regional and language options. I can namely see from your example that when excel displays date correct it does not display it as date (if the date is aligned left), but as text. Try to change this date options to dd/mm/yyyy and then open this txt file. If dates are displayed correctly, save the file as excel (dates will be saved as dates) and then change date format in control panel. If you change it for example to dd.mm.yyyy, excel should display dates as dd.mm.yyyy. Regards, Nika Lampe "notMe" je napisal: Sorry for the lengthy post... 1- explanation With File Explorer double-clicking on a tab-delimited text file is not the same as drag'n'dropping that file into an occurrence of Excel. All dates are changed to another format with double-clicking, they are kept 'as is' with drag'n'drop. With double-clicking : Dates are standard ISO (dd/mm/yyyy ) but Excel *transforms* the actual data into mm/dd/yyyy. The swapping of dd and mm only occurs when dd < 13. Otherwise the field is kept left-aligned like a text filed. day(<myCell) gives different results depending on how the file was opened so it is not a mere formatting problem. The data is altered by Excel and it does not seem right. At least it should always be done consistently. 2- example : Text file : foo.gs A <tab 16/09/2006 B <tab 08/09/2006 Displayed in Excel after double-clicking : A.........B..........DAY(B) A 16/09/2006 16 B 09/08/2006 09 <= wrong value : why swap dd and mm at all ? Displayed in Excel after dragging : A.........B..........DAY(B) A 16/09/2006 16 B 08/09/2006 8 Any way to fix this ? This is on a French Windows XP system, set to French(France) and the formatting for date is dd/mm/yyyy. I can't change this because other apps need this setting. Not sure Excel uses it either! Files have a .gs extension and are associated with Excel in File Explorer options menu. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
different values depending on how text file is opened
Thank you everybody for sharing your thoughts.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text File creates incorrect Date format | Excel Discussion (Misc queries) | |||
How to hide a text file using macro | Excel Discussion (Misc queries) | |||
Link Protected WorkBook ... and Get #N/A for Text Values! | Excel Discussion (Misc queries) | |||
Filling Text from Another File | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |