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