Am 03.03.2012 14:19, Duncan Murdoch wrote:
I have recently switched from OpenOffice to LibreOffice (version 3.5.0),
and am having problems configuring it to handle dates in the same way.
These are the problems:
1. I want to maintain a CSV file containing dates in the format
DD/MM/YY. When I read that file into OO, the columns containing those
dates were automatically recognized as date columns, not text columns,
and the dates were read correctly. Is there a way to configure LO to do
For example, some typical lines at the top of the input file might look
and the dates are March 2, 2012 on the first line, March 1, 2012 on the
1) OOo 3.3 and LibO have a locale setting as import option. Any locale
setting other than English(US) might import 2/3/12 correctly. Just try
2) Check the "special numbers" option which evaluates any numeric
expression even if it is not a plain decimal.
2. I would also like the dates to be displayed in the same format within
LO. Currently it thinks those lines are text, and displays them like
that, but of course sorting fails. If I manually tell it that those are
DMY dates when I read the file, they are displayed as YYYY-MM-DD format
after reading. I can manually set the format to the user defined
DD/MM/YY format, but it doesn't display that way in the data entry line,
it displays as YYYY-MM-DD format.
This is impossible to do by means of direct text import. Calc
interpretes the incoming raw text data according to your import settings
and enters the resulting values into a brand new unformatted sheet. CSV
includes raw data for data exchange between database applications.
1) With the help of the Base component you can import the raw data into
2) You may use the built-in database engine to create a customized edit
form for your particular flavour of csv where the entered data always
produce a specified flavour of csv.
3) I use to recommend this tiny but extremely useful text editor for
3. If I manually enter a date into a cell, I would like the same thing
to happen. Currently if I type 02/03/12 as data, it is converted to the
Nothing wrong with the ISO format as long as the value is the same as
intended value. You are free to apply any locale setting (globally or
document specific) and any number formatting you like. This has no
effect on your cell values.
4. If I write that CSV file back out to CSV, I would like it to maintain
the same DD/MM/YY format.
A calculator is definitively not the most appropriate tool to edit and
write database data.
I suspect all of this could be fixed if I could find some localization
setting that says dates are by default formatted as DD/MM/YY on both
input and output, but I can't find that setting. Is there such a thing?
1) Tools>Options>LanguageSettings>Languages: "Locale Setting". This is
the default for all numerals in the entire suite, including brand new
2) Cell Stlye "Default", number format locale: overrides the locale for
all dependent cell styles of this document. Does not affect brand new
3) Number format locale of any other cell style: Overrides the locale
for a certain type of formatted cells.
4) Hard formatting the number format locale menu:Format>Cells...
overrides all styles for the current cell selection
But the last 4 points do not override the general concept that raw text
data get imported into a completely unformatted new sheet. In this
particular case the global locale setting "English(UK)" may tweak that
unformatted new sheet so it meets your requirement.
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy