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