At 11:48 01/04/2010 +0100, Harold Fuchs wrote:
OOo 3.1.1 on Win XP Pro.
I have a column of dates in a text (.txt) file and want to copy them
into a column of a Calc sheet. I can't because Calc insists on
changing the values.
That's what spreadsheets do, of course. ;^)
The first few lines of the .txt file look like this:
Fri Apr 30
Fri Apr 30
Sat May 1
Sat May 1
Sun May 2
Sun May 2
Mon May 3
All the dates are *this* year.
If I simply copy/paste those values into Calc I get
01/04/30
01/04/30
01/05/01
01/05/01
01/05/02
01/05/02
01/05/03
Calc has ignored the day names and decided that your month names are
indeed months but that your day numbers are years.
If I format the column "DDD MM DD" which should show e.g. Mon 18 May, then I
get
Tue Apr 01
Tue Apr 01
Tue May 01
Tue May 01
Wed May 01
Wed May 01
Thu May 01
Right format but wrong values.
I think you must mean "DDD MMM DD", but no matter. These are indeed
the wrong values, but the display is correct for what is in the
cells. The problem occurred earlier, with the automatic editing when
you pasted the material.
Note that in each case, the first date shows on the Input line as
01/04/1930, the last as 01/05/2003.
Yes: two-digit year values (which Calc thinks you are providing) are
interpreted by default as between 1930 and 2029.
The "dd/mm" style is right from me because I use European format as
opposed to the US style "mm/dd".
There is a clue here to a solution. Your original text is in
month-day order, not the default for your UK settings. Use Paste
Special instead of Paste. In the Text Import window, click the word
Standard at the head of the column of values, and then choose US
English from the "Column type" drop-down menu. Format your cells
appropriately either before or after you paste.
Alternatively, a simple but tedious solution is to use spreadsheet
functions to strip apart the different parts of the date and
reassemble them appropriately. This solution is easier if you choose
Text as "Column type" in the Text Import window: that way your
original text is not edited. But you now have text values, not
dates, and you will have to reassemble then into what you need.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
