Re: [libreoffice-users] Re: Help with date formatting

Thanks, I'll try your suggestions.  The reasons I've chosen to do things 
this way:
  - Other software needs to read the file too.  I'd like to avoid 
changing the format of the files, because then everything would need 
converting before the other software could read it.

  - I want to be able to cut and paste lines from a web page into the 
file, to make small updates.  This is why I'm using Calc for editing. 
OO lets me paste data and it gets treated as I want.  Hopefully with 
your suggestions for configuring LO, it'll do the same.

Duncan Murdoch

On 12-03-03 8:49 AM, Andreas Säger wrote:
> 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
> > the same?
> >
> > For example, some typical lines at the top of the input file might look
> > like
> >
> > "Date1","Date2","Text1","Value1","Text2","Text3","Value2","Value3","Value4"
> > 02/03/12,02/03/12,"TEXT",,,"TEXT",,,-100
> > 01/03/12,01/03/12,"TEXT",1,"TEXT","TEXT",,,5.95
> >
> > and the dates are March 2, 2012 on the first line, March 1, 2012 on the
> > second.
>
> 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
> English(UK).
> 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
> preformatted sheets.
> 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
> csv: http://csved.sjfrancke.nl/
>
> > 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
> > date 2002-03-12.
> 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?
> >
> > Duncan Murdoch
>
> 1) Tools>Options>LanguageSettings>Languages: "Locale Setting". This is
> the default for all numerals in the entire suite, including brand new
> unformatted sheets.
> 2) Cell Stlye "Default", number format locale: overrides the locale for
> all dependent cell styles of this document. Does not affect brand new
> unformatted sheets.
> 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.


--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted