On 1 April 2010 14:15, Brian Barker <[email protected]> wrote:
> 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. > Thanks, Brian. The subsidiary question is how does one know to do that ??? <snip> -- Harold Fuchs London, England Please reply *only* to [email protected]
