Results 1 to 9 of 9

Thread: LibreOffice calc question - cell format

  1. #1
    Join Date
    Feb 2009
    Location
    Spain
    Posts
    25,547

    Default LibreOffice calc question - cell format

    Hi,

    When I press ctrl-shitf-3, or the equivalent button in the menu, to
    change a cell to date format, it uses a date format that is not the one
    I prefer.

    Is it possible to choose what is the default date format? Per document,
    for all documents, or both.

    :-?

    It forces me to continuously navigate on the cell format menu to choose
    mine. Takes time.

    --
    Cheers / Saludos,

    Carlos E. R.

    (from 13.1 x86_64 "Bottle" (Minas Tirith))

  2. #2
    Join Date
    Jul 2008
    Location
    Seattle, WA
    Posts
    17,076

    Default Re: LibreOffice calc question - cell format

    On Mon, 04 Aug 2014 20:48:11 +0000, Carlos E. R. wrote:

    > Hi,
    >
    > When I press ctrl-shitf-3, or the equivalent button in the menu, to
    > change a cell to date format, it uses a date format that is not the one
    > I prefer.
    >
    > Is it possible to choose what is the default date format? Per document,
    > for all documents, or both.
    >
    > :-?
    >
    > It forces me to continuously navigate on the cell format menu to choose
    > mine. Takes time.


    Tools->Options->Language Settings->Locale

    Does that do what you're looking for? (I noticed that it switches the
    format to D/M/Y instead of M/D/Y here)

    --
    Jim Henderson
    openSUSE Forums Administrator
    Forum Use Terms & Conditions at http://tinyurl.com/openSUSE-T-C

  3. #3
    Join Date
    Feb 2009
    Location
    Spain
    Posts
    25,547

    Default Re: LibreOffice calc question - cell format

    On 2014-08-05 00:11, Jim Henderson wrote:

    > Tools->Options->Language Settings->Locale
    >
    > Does that do what you're looking for? (I noticed that it switches the
    > format to D/M/Y instead of M/D/Y here)


    That sets the date to whatever is the standard for your language and
    country, which happens to be here D/M/Y. But I use the ISO standard
    instead (8601), that is, "AAAA-MM-DD".

    In that dialog you say there is an entry named "date acceptance
    patterns", which is set to "D/M/Y;D/M", but that is not, I understand,
    the display format, but the format that you can use for entering dates.


    This is more specific, like setting what number format you want to use
    by default. I don't know where to set up these things, if at all possible.


    --
    Cheers / Saludos,

    Carlos E. R.

    (from 13.1 x86_64 "Bottle" (Minas Tirith))

  4. #4
    Join Date
    Jul 2008
    Location
    Seattle, WA
    Posts
    17,076

    Default Re: LibreOffice calc question - cell format

    On Mon, 04 Aug 2014 23:14:09 +0000, Carlos E. R. wrote:

    > On 2014-08-05 00:11, Jim Henderson wrote:
    >
    >> Tools->Options->Language Settings->Locale
    >>
    >> Does that do what you're looking for? (I noticed that it switches the
    >> format to D/M/Y instead of M/D/Y here)

    >
    > That sets the date to whatever is the standard for your language and
    > country, which happens to be here D/M/Y. But I use the ISO standard
    > instead (8601), that is, "AAAA-MM-DD".
    >
    > In that dialog you say there is an entry named "date acceptance
    > patterns", which is set to "D/M/Y;D/M", but that is not, I understand,
    > the display format, but the format that you can use for entering dates.
    >
    >
    > This is more specific, like setting what number format you want to use
    > by default. I don't know where to set up these things, if at all
    > possible.


    Looks like you can set it in the template that's used by default.

    http://ask.libreoffice.org/en/questi...e-the-default-
    date-format-in-calc-to-show-4-digit-years/ is one of a few pages that a
    google search turned up - but it seems that this is a frequently
    requested feature.

    Jim



    --
    Jim Henderson
    openSUSE Forums Administrator
    Forum Use Terms & Conditions at http://tinyurl.com/openSUSE-T-C

  5. #5
    Join Date
    Feb 2009
    Location
    Spain
    Posts
    25,547

    Default Re: LibreOffice calc question - cell format

    On 2014-08-05 03:19, Jim Henderson wrote:
    > On Mon, 04 Aug 2014 23:14:09 +0000, Carlos E. R. wrote:



    >> This is more specific, like setting what number format you want to use
    >> by default. I don't know where to set up these things, if at all
    >> possible.

    >
    > Looks like you can set it in the template that's used by default.
    >
    > http://ask.libreoffice.org/en/questi...e-the-default-
    > date-format-in-calc-to-show-4-digit-years/ is one of a few pages that a
    > google search turned up - but it seems that this is a frequently
    > requested feature.


    I should have thought about the default document template. O:-}

    Thanks for googling it; I'm not at home, I have a limited internet
    connection, so browsing about is not something I can do. I thought that
    somebody here might have done this thing before and remember it ;-)


    The procedure appears to be:

    Create new spreadsheet.

    Open styles and formatting (F11)

    The first entry is "Default". Right click, Modify. You get a dialog. On
    this dialog, select the "numbers" tab. Now you can see the settings for
    each type of cell entries: numbers, text, dates, etc.

    Then save as default template; the help index explains this, just search
    for "default templates;changing".


    However... this is all wrong!

    What this does is setting the default cell style, that applies to
    anything you enter on any cell, to "date"!


    This will not do.


    The wanted feature is something that defines the default text style,
    default number style, default date style, default time style, default
    currency style... Not something that says that the default style for
    all cells is "date" with a certain date format.




    (reading again the link...)


    Ok, I read it wrong. This is my third attempt at it this night - I'll
    blame that I crashed my head into the window sill earlier this night,
    because the window blind broke down with a crash as I opened it, and I
    lost my balance O:-)


    What they say is not to modify the default cell style of the default
    template, but to create a new cell style, and save it in the default
    template. Then when you write a cell and enter a date, apply to it that
    new style of yours...

    But again, this will not do, it is not good enough.

    Caveats/reasons:

    * If I open an existing document, I don't have that style available, I
    have to create it again, and apply it to all wanted cells - same as I do
    now, changing cell format of all date cells.

    * If I use the "date format" button, it will not apply my (new) style
    for date, it will apply the current locale style for date.



    By the way, it is not applying the user "locale", either:

    Code:
    cer@minas-tirith:~> locale
    LANG=en_US.utf8
    LC_CTYPE="en_US.utf8"
    LC_NUMERIC="en_US.utf8"
    LC_TIME=en_DK.UTF-8      <---
    LC_COLLATE="en_US.utf8"
    LC_MONETARY=es_ES@euro
    The "en_DK" locale for date happens to to be ISO.

    --
    Cheers / Saludos,

    Carlos E. R.

    (from 13.1 x86_64 "Bottle" (Minas Tirith))

  6. #6
    Join Date
    Jun 2008
    Location
    Auckland, NZ
    Posts
    20,549
    Blog Entries
    1

    Default Re: LibreOffice calc question - cell format

    I can understand your requirement here, and I think you need to escalate with a feature request

    https://www.libreoffice.org/get-help/feedback/

  7. #7
    Join Date
    Feb 2009
    Location
    Spain
    Posts
    25,547

    Default Re: LibreOffice calc question - cell format

    On 2014-08-05 08:56, deano ferrari wrote:
    >
    > I can understand your requirement here, and I think you need to escalate
    > with a feature request
    >
    > https://www.libreoffice.org/get-help/feedback/


    I'm sure that has been requested before, but my very limited internet
    connection at the moment does not allow me to investigate. I need to go
    back to the city and my desktop machine and adsl.

    But thanks, I'll add it to my to-do list...

    --
    Cheers / Saludos,

    Carlos E. R.

    (from 13.1 x86_64 "Bottle" (Minas Tirith))

  8. #8
    Join Date
    Dec 2008
    Location
    Montana, USA
    Posts
    550

    Default Re: LibreOffice calc question - cell format

    I did a little looking, testing. I found that indeed, you must set the format for each cell, rather than for a column. I notice I almost always use a column for dates, so had not had your problem. The interesting part... I placed a date in the top cell of a row. I entered a date and the cell was automagically formatted to date format. It matched the settings for a date as shown for my locale. (I'm using rub-point-click here) I drug the cell handle down and created a column of dates. I went to the format menu and tried different date formats to see if the entire column would change. it did. I then used Ctrl-Shift-3 and the column changed in the pattern I had tested.

    My observation is: You can change the first cell you enter as a date. Most dates are in a column. You can highlight a column of dates by clicking on the letter of the date. Ctrl-Shift-3 will change the format to the one you selected.

    Not exactly what you wanted, but not too much time lost either. Hope it helps.

    Bart

  9. #9
    Join Date
    Feb 2009
    Location
    Spain
    Posts
    25,547

    Default Re: LibreOffice calc question - cell format

    On 2014-08-06 14:26, montana suse user wrote:
    >
    > I did a little looking, testing. I found that indeed, you must set the
    > format for each cell, rather than for a column. I notice I almost
    > always use a column for dates, so had not had your problem. The
    > interesting part... I placed a date in the top cell of a row. I
    > entered a date and the cell was automagically formatted to date format.
    > It matched the settings for a date as shown for my locale. (I'm using
    > rub-point-click here) I drug the cell handle down and created a column
    > of dates. I went to the format menu and tried different date formats to
    > see if the entire column would change. it did. I then used
    > Ctrl-Shift-3 and the column changed in the pattern I had tested.
    >
    > My observation is: You can change the first cell you enter as a date.
    > Most dates are in a column. You can highlight a column of dates by
    > clicking on the letter of the date. Ctrl-Shift-3 will change the format
    > to the one you selected.
    >
    > Not exactly what you wanted, but not too much time lost either. Hope it
    > helps.


    Let's try.

    I enter a date in A1: "15/08/2014". It automatically displays as
    "15/08/14", which is the default locale according to libreoffice.

    I then change the cell format to "AAAA-MM-DD", the ISO standard.

    Then I enter on cell A2 another date: "16/08/2014", and it automatically
    changes to "16/08/14"

    What I have been doing for ages is to highlight a range, and change its
    cell format. I was not aware that you can change the format of an entire
    column - it is some progress, yes. Thanks.

    Code:
    -rw-r--r--  1 cer users     8539 Aug  6 14:46 p1.ods
    -rw-r--r--  1 cer users     8354 Aug  6 14:46 p2.ods
    The sizes of the file changes very little - the second one has column
    format applied.

    If I enter a new date on that column it does get formatted as I defined,
    yes.


    I then enter a third cell with a date, on C3, as "17/08/2014".
    Ctrl-Shift-3 does not change to ISO date format. I have to format it
    manually, as always. Selecting the entire column does the same, the
    format it uses is "DD/MM/AA", not my wanted one, "AAAA-MM-DD".


    What I want is to change the format applied by Ctrl-Shift-3 to one of my
    choice. And that setting to be applied globally to all my documents, old
    or new. Said otherwise: I want to configure the default date format. To
    customize the locale.

    --
    Cheers / Saludos,

    Carlos E. R.

    (from 13.1 x86_64 "Bottle" (Minas Tirith))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •