JOE Conner wrote:
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.
Start with a brand new sheet and I think you'll find that what you wrote
isn't entirely accurate.
When I type into a cell already formatted as Date/YYYY-MM-DD, the entry
is always displayed as called for by the format, as long as the value I
enter is recognized as numeric for my locale, i.e. not a string.
I can enter: 1/1, 09-01, 39814.0, or 3.9814e4 and they all display as
2009-01-01, according to the format.
However, if I enter a text value: Xyz, or '1/1, or even '2009-01-01,
they will be displayed as entered. The last one may look like a date,
but it's not.
Copy/pasting a cell is not the same as entering a value. When you paste
a cell, or any other source of formatted material, Calc tries to
preserve the formatting of the _source_ not the destination. If you want
to preserve the cell format when you paste something, you have to use
Edit > Paste Special as unformatted text, or excluding the cell format.
I suspect that some of your dates may actually be text. You can see this
if you press Ctrl+F8 to toggle "value highlighting": numeric dates will
appear in blue, text values in black.
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. ...
Changing the format of a cell /never/ changes the data that is stored in
the cell. The format can only change the way the data is displayed. If
the value in the cell is not the matching type (i.e. text value/date
format), then Calc uses its default format to display the item.
It's only when a value is entered that Calc examines it to see what kind
of value it is, text or number.
Fortunately, Edit > Find & Replace counts as 'entering' a value, even if
the replacement is exactly the same as the original. So, you can use F&R
to make mass changes of the data's type:
Select your cells, then Edit > Find & Replace
Search for: .*
Replace with: &
Options/Regular expressions: YES
Options/Current selection only: YES
Replace All
Note 1: For dates to be converted to numeric values by this, the text
must be recognized as a valid date _in your locale_. E.g. if I have text
in a cell, "1.1.09" or "31/1/09" they will not be converted to numeric
values because they are not valid dates under my locale (US English),
even though they are valid dates in other locales. Calc will recognize
an ISO 8601 date in any locale.
Note 2: Using F&R like this can actually modify the data, so I don't
recommend it unless you're sure you understand what you're doing. If
Ctrl+F8 only shows a few text values, I would suggest that you fix them
manually.
<Joe
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
