Brian Barker wrote:
At 22:54 31/03/2009 +1100, Dave Barton wrote:
From: Joe Conner <[email protected]>
Date: Mon, 30 Mar 2009 23:26:24 -0700
Dave Barton wrote:
Format an empty Calc cell with an ISO 8601 date format (ie.
YYYY-MM-DD) and then type, or paste, a date in US format (ie.
MM/DD/YYYY) into the cell and the date is displayed as entered.
Reformatting the cell after the date is entered does not change it
to ISO 8601 format.
My understanding was that Calc recognised different date formats and
translated them according to the cell format. If this is not the
case, could anyone suggest a method of translating the dates in an
existing 600+ row sheet from MM/DD/YYYY to YYYY-MM-DD
Calc stores dates as numbers. I suspect that if you reformat the
cells as numbers they would all convert. Then once again reformat the
cells as the date string you need.
For some cells your suggestion works and others remain unchanged. I
guess there must be something else in the sheet which is influencing
the formatting. I can't imagine what that "something" could be, since
this sheet was originally a simple csv text file.
I imagine that what happens here may depend on locale, so what I see and
say may not be true for you or others. I don't experience exactly what
you say, in fact: if I type or paste as you suggest, again the result is
that some dates are reformatted and some not.
I think the problem is simple: Calc has to guess what you mean by your
typed or pasted date, and XX/XX/XXXX could mean MM/DD/YYYY or
DD/MM/YYYY. For me Calc interprets such forms as DD/MM/YYYY. I imagine
that this is because of my locale setting - English (UK) - but I have
not tested this. So a date such as 04/01/2009 will be recognised and
interpreted as a date but one such as 03/31/2009 will not be. But note
specifically that 04/01/2009 is interpreted (in my locale) *incorrectly
for your needs* as 4 January. So I get a mixture of three things: text
strings, wrong dates, and a few dates correct by coincidence.
How you sort this out depends on exactly what is in your cells: if you
see 03/31/2009 you could be seeing a date formatted in the US style or a
text string. But if you have the original CSV file, you should be able
to retrieve the data in simple text format anyway. Then you can use
something such as
=DATE(RIGHT(xx;4);LEFT(xx;2);MID(xx;4;2))
to strip the parts of the text and reassemble them as a date. You may
then want to freeze the results as values by copying them and pasting
them back, but using Paste Special... with Formulas *not* ticked.
Oh, another - possibly easier - technique is to paste the material in
from a plain text source, bringing up the Text Import dialogue. You
could then select slash as the separator, which would put the three
parts of your dates into separate cells. The formula above could then
be simplified into something such as
=DATE(Cx;Ax;Bx)
This route might deal more readily with any rogue blanks and with any
single digits for days or months.
I trust this helps.
Brian Barker
Or go back to the original .CSV file and re-import it to a new sheet.
This time go through each column and manually force it to import any
column with a date as a date formatted column. I suspect that on your
original import you got a few date columns formatted as text.
--
Gene Y.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
