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
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
