"Brian Barker" <[email protected]> wrote in message
news:[email protected]...
At 13:31 28/11/2010 +0000, Harold Fuchs wrote:
OOo 3.2.1 Vista Home Premium, Calc
I have a web page showing a table of dates and associated events. The
dates, all in the same year (2011) are in the style, for example "Sun Oct
23". If I copy/paste the table into Calc, the example date I just gave
comes across as 01/10/2023. How do I fix this so that the dates come
across "correctly"?
Calc's Paste Special option doesn't seem to help.
I have tried saving the table as a CSV file by copy/pasting the web table
into a text editor and then opening that file in Calc but the result is
the same.
I don't think you can achieve what you really want: some way of pasting
this that gives rise directly to correct dates. You have two pasting
options, I think: with General formatting, you see a wrongly interpreted
date; with cells previously formatted as Text, you will preserve the
original form. In either case, you then need a little processing to form
the original date that you need.
o In the first case (where you see "01/10/2023"), try:
=DATE(2011;MONTH(A1);MOD(YEAR(A1);100))
o In the second case (where you see "Sun Oct 23"), try:
=DATEVALUE(MID(A1;9;2)&" "&MID(A1;5;3)&" 2011")
In this case, you will need to format the results cell as Date to see what
you need.
You can either paste the results back over the originals (using Paste
Special...) or hide the original values if you prefer, of course.
I trust this helps.
Brian Barker
Yes, thanks Brian. In the end I appended " 2011" to each date using a text
editor and a regular expression; luckily the data was easily amenable to
this trick.
--
Harold Fuchs
London, England
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
