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. |
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 |