LibO 3.6.0.2 - Calc: date notation

Guy_Voets · July 23, 2012, 12:44pm

Hi folks,

A LibO spreadsheet, made in LibO, Dutch version (no Excel or OOo past).

   - In LibO 3.5.5, I used to give in dates as 20-7 and they were shown as
   20 Jul 12.
   - In LibO 3.6.0.2, if I enter 20-7, 20-7 is shown in the cell.

If I enter 20-7-12, the date is inverted into 12 Jul 2020.
So instead of entering 20-7, I now need to enter 12-7-20 to get the desired
notation 20 Jul 12.

Is this a new feature, or a bug?

Johnny_Rosenberg · July 23, 2012, 1:29pm

Hi folks,

A LibO spreadsheet, made in LibO, Dutch version (no Excel or OOo past).

   - In LibO 3.5.5, I used to give in dates as 20-7 and they were shown as
   20 Jul 12.
   - In LibO 3.6.0.2, if I enter 20-7, 20-7 is shown in the cell.

If I enter 20-7-12, the date is inverted into 12 Jul 2020.
So instead of entering 20-7, I now need to enter 12-7-20 to get the desired
notation 20 Jul 12.

Is this a new feature, or a bug?

I am a user, not a developer, so I don't know for sure, but it looks
to me as a feature rather than a bug, or maybe a combination of both.
20-7-12 means 2020-july-12 (12th July 2020) according to ISO 8601, and
I guess it could be a point adapting to this international standard
(some countries already adapted most of ISO 8601, some of them decades
ago). Maybe this behaviour should be more dependent of the current
language settings?

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

elderdanlewis · July 23, 2012, 2:52pm

Guy Voets wrote:

Hi folks,

A LibO spreadsheet, made in LibO, Dutch version (no Excel or OOo past).

    - In LibO 3.5.5, I used to give in dates as 20-7 and they were shown as
    20 Jul 12.
    - In LibO 3.6.0.2, if I enter 20-7, 20-7 is shown in the cell.

If I enter 20-7-12, the date is inverted into 12 Jul 2020.
So instead of entering 20-7, I now need to enter 12-7-20 to get the desired
notation 20 Jul 12.

Is this a new feature, or a bug?

      I just downloaded LO 3.6.2 and installed it. Then I opened Calc. I also entered 20-7 in a cell and got 20-7. It seems that Calc considered that to be text because the window to the right of the = was showing '20-7.
      Then I right clicked a column and formatted it using Dutch (Netherlands) as the local. I also selected 31 dec 99 from the format list. Then when I entered 20-7 I got 20 jul 2012. 14-7 gave me 14 jul 2012.
      It may be a new "feature" in that you have to format cells, rows, or columns for the type of formatting you want in them.

--Dan

Guy_Voets · July 23, 2012, 2:53pm

Hi Johnny, thanks.

I get your point...
The language/country setting is Dutch/Belgium, which normally should give
20-7-2012, and I liked the practicality of just having to type 20-7 to get
this date entered in the spreadsheet.
I suppose I'll have to get used to the ISO standard behaviour.

elderdanlewis · July 23, 2012, 3:40pm

Guy Voets wrote:

Hi folks,

A LibO spreadsheet, made in LibO, Dutch version (no Excel or OOo past).

- In LibO 3.5.5, I used to give in dates as 20-7 and they were shown

as

20 Jul 12. - In LibO 3.6.0.2, if I enter 20-7, 20-7 is shown in the cell.

If I enter 20-7-12, the date is inverted into 12 Jul 2020. So instead of entering
20-7, I now need to enter 12-7-20 to get the

desired

notation 20 Jul 12.

Is this a new feature, or a bug?

I am a user, not a developer, so I don't know for sure, but it looks to me as a
feature rather than a bug, or maybe a combination of both. 20-7-12 means 2020-july-12
(12th July 2020) according to ISO 8601, and I guess it could be a point adapting to
this international standard (some countries already adapted most of ISO 8601, some of
them decades ago). Maybe this behaviour should be more dependent of the current
language settings?

Kind regards

Johnny Rosenberg ジョニー・ローゼンバーグ

Hi Johnny, thanks.

I get your point... The language/country setting is Dutch/Belgium, which normally should
give 20-7-2012, and I liked the practicality of just having to type 20-7 to get this
date entered in the spreadsheet. I suppose I'll have to get used to the ISO standard
behaviour.

Looking at the date formats available for Dutch/Belgium, there is no DD-MM-JJ. If you
create a user defined format with this, you will be able to enter your dates as you always
have. You just have to format the cells or columns first using this format.

--Dan

mariosv · July 23, 2012, 3:49pm

And maybe is refined to be interpreted as date using the system separator, the same separator as in the date cell format.

In my Spanish Winx64Ult: /

If I enter: 20/7
I get: 20/07/12

If I enter: 20-7
I get: 20-7 (text)

Miguel Ángel

  * Inglés - detectado
  * Inglés
  * Español
  * Gallego
  * Italiano

  * Inglés
  * Español
  * Gallego
  * Italiano

<javascript:void(0);>

leif.lodahl · July 23, 2012, 5:01pm

Hi,
The changed behavior cold be related to this feature:
http://erack.org/blog/archives/8-LibreOffice-date-acceptance-patterns.html

Cheers,
Leif Lodahl

Guy_Voets · July 23, 2012, 5:28pm

2012/7/23 Leif Lodahl <leiflodahl@gmail.com>

Hi,
The changed behavior cold be related to this feature:
http://erack.org/blog/archives/8-LibreOffice-date-acceptance-patterns.html

Cheers,
Leif Lodahl

Thanks Leif, I'll try & adapt my behaviour...

--
Guy
using LibO 3.6.0 on a iMac Intel DualCore Lion 10.7.4
-- please reply only to users@global.libreoffice.org --
Dodoes can't afford to have headaches

Tom_Davies · July 23, 2012, 7:02pm

Hi

I find that is often the way too. It's a bit contra-intuitive to type-in a / when you know you want it to display a - and that makes it very hard to explain to people that need to enter data into your spreadsheet. Saying it's the same in Excel doesn't help.
Regards from
Tom

Villeroy · July 23, 2012, 7:02pm

Am 23.07.2012 14:44, Guy Voets wrote:

Hi folks,

A LibO spreadsheet, made in LibO, Dutch version (no Excel or OOo past).

    - In LibO 3.5.5, I used to give in dates as 20-7 and they were shown as
    20 Jul 12.
    - In LibO 3.6.0.2, if I enter 20-7, 20-7 is shown in the cell.

If I enter 20-7-12, the date is inverted into 12 Jul 2020.
So instead of entering 20-7, I now need to enter 12-7-20 to get the desired
notation 20 Jul 12.

Is this a new feature, or a bug?

This is just another anti-feature that has been added to Calc against all reason simply because too many inexperienced users who never really used any spreadsheets insisted loudly enough.
I will upgrade my LibreOffice 3.5 to ApacheOpenOffice 3.4.1.

Tom_Davies · July 23, 2012, 7:04pm

Hi
Surely it's just a carelessness that can be undone?

Is there a bug-report we can petition?
Regards from
Tom

Joep_L_Blom · July 23, 2012, 9:18pm

I resent the US way of ISO 8601. We Dutch and other Europeans use the more logical sequence of day-month-year instead of the illogical year-month-day.(most important first, least important last: very often the year can be missed).
Joep

Tom_Davies · July 23, 2012, 10:14pm

Hi
I thought the USA way was the amazingly weird
mm/dd/yy

Apparently it's important to use / instead of - in order to make sure it's easier to mis-read. With some people's handwriting an 11 might look like 1/ or vice-versa.
Regards from
Tom

Johnny_Rosenberg · July 23, 2012, 10:14pm

I never heard that one before… Isn't the year more important than the day?
Do you prefer SS:MM:HH too?

To me, ISO 8601 (which happens to be the same as the Swedish and a few
more standards in this matter…) is logical: Long time to the left,
shorter time to the right: Year, month, day, hour, minute, second,
decimals. Anyway, no matter if you prefer year-month-day or
day-month-year, there will be no problem to understand what is
written, if the year is written with four digits (five after the year
10000, if humans exist by then…).

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

planas · July 23, 2012, 10:41pm

Am 23.07.2012 14:44, Guy Voets wrote:

Hi folks,

A LibO spreadsheet, made in LibO, Dutch version (no Excel or OOo past).

    - In LibO 3.5.5, I used to give in dates as 20-7 and they were
shown as
    20 Jul 12.
    - In LibO 3.6.0.2, if I enter 20-7, 20-7 is shown in the cell.

If I enter 20-7-12, the date is inverted into 12 Jul 2020.
So instead of entering 20-7, I now need to enter 12-7-20 to get the
desired
notation 20 Jul 12.

Is this a new feature, or a bug?

This is just another anti-feature that has been added to Calc against
all reason simply because too many inexperienced users who never really
used any spreadsheets insisted loudly enough.
I will upgrade my LibreOffice 3.5 to ApacheOpenOffice 3.4.1.

I resent the US way of ISO 8601. We Dutch and other Europeans use the more logical sequence of day-month-year instead of the illogical year-month-day.(most important first, least important last: very often the year can be missed).

The standard US date convention is even less logical than ymd or dmy it is mdy. Ymd and dmy both have a progression that makes sense. I live in the US.

planas · July 23, 2012, 10:44pm

Hi
I thought the USA way was the amazingly weird
mm/dd/yy

It can tricky to enter dates as part of a file name in the US since / is reserved for use in OS path descriptions.

elderdanlewis · July 23, 2012, 11:02pm

Let me see: open Calc in LO 3.6.0.2 and format a column selecting the Category as Date and the Language as English(UK). It does not seem to matter what is selected as the Format. (I selected 31/12/99.) Enter 20-7 in a cell. It becomes 20/7/12. When 20/7 is entered in a cell of the column, 20/7/12 is the result.
It is a matter of formatting the column, cell, or row for the type of data to be placed in the sheet. With the correct format [English(USA)], I can enter 20-7 in a cell, and it will become Saturday, July 20,2012 or Saturday, 20 July 2012 depending upon what format I use. (The last one would require selecting User-defined Category and the appropriate entries in the Format code box.)
Ah yes, the "weird" USA way. While I had the Format dialog open with UK as the Language, I noticed something in the list of Format examples: MM-DD! If it should be DD/MM/YY, then why should it also be MM/DD? OK so the USA way is weird, but then so is the British. Check it out. Chuckle, Chuckle! (From where this is located in the Format example list, I think I know why it is this way. (ISO 8601) But I could not resist replying to Tom's comment.

--Dan

Tom Davies wrote:

elderdanlewis · July 23, 2012, 11:31pm

Dan wrote:

      Let me see: open Calc in LO 3.6.0.2 and format a column selecting
the Category as Date and the Language as English(UK). It does not seem
to matter what is selected as the Format. (I selected 31/12/99.) Enter
20-7 in a cell. It becomes 20/7/12. When 20/7 is entered in a cell of
the column, 20/7/12 is the result.
      It is a matter of formatting the column, cell, or row for the type
of data to be placed in the sheet. With the correct format
[English(USA)], I can enter 20-7 in a cell, and it will become Saturday,
July 20,2012 or Saturday, 20 July 2012 depending upon what format I use.
(The last one would require selecting User-defined Category and the
appropriate entries in the Format code box.)
      Ah yes, the "weird" USA way. While I had the Format dialog open
with UK as the Language, I noticed something in the list of Format
examples: MM-DD! If it should be DD/MM/YY, then why should it also be
MM/DD? OK so the USA way is weird, but then so is the British. Check it
out. Chuckle, Chuckle! (From where this is located in the Format
example list, I think I know why it is this way. (ISO 8601) But I could
not resist replying to Tom's comment.

--Dan

Sorry folks, but this is too good to be kept a "secret." Source: Wikipedia, article: Calendar Date. Here is a quote from it:

"This sequence is used primarily in the United States, partially in Canada, and a few other countries[citation needed]. This date format was commonly used alongside the small endian form in the United Kingdom until the early 20th Century, and can be found in both defunct and modern print media such as the London Gazette and The Times, respectively. In the UK, it would be verbally expressed as Sunday, November the 9th, whereas in the United States, it is usually Sunday, November 9th, although usage of "the" isn't uncommon."

So now we know where the USA got its weird format for dates: from the UK! Particularly from London England.
Oh happy day! Big Smile!

--Dan

nvrk · July 24, 2012, 1:35am

FWIW - I prefer ddmmmyyyy or yyyymmmdd; 09Jul2012 or 2012Jul09;
unambiguous; with minimum characters! --nvsoar

Tom_Davies · July 24, 2012, 7:16am

Hi
The fact of something appearing in a format dialogue box is not really proof of that format being used! I've never seen nor heard of the USA way being used in the UK. Perhaps it's used in the US airbases. Wouldn't it be a tad confusing if both ways were really being used here? I think you have found a bug in the format dialogue but i would be amazed if those dialogues are really specific to a locale setting.
Regards from
Tom