Eternal bug - these calc dates

English
,
#1

Hi LibreOffice,

DATE FORMATS
Firstly:
In the Date Format dropdown box there are date formats “31. Dec. 1999” and “31. December 1999”. Let this be my formal stern protest against these frustrating entries. Those full stops do not belong there in my locale, and they prevent me from using universally standard formats.

DATE REPLACING

  • I am in South Africa, locale set to South Africa, where we place the month portion of a date in centre of 3 and 4-component date formats, viz. dmy ; ymd ; ddd, d mmm y

  • In my Calc Workbook, 12 of my columns are labelled to be the value of the 1st of each month, from March this year to February next year, formatted to display these values in the format “mmm yyyy”, so user sees Mar 2024, Apr 2024, May 2024, et cetera, up to Feb 2025.

  • That’s my Financial Year 2025 Workbook. Now I’ve saved it as my 2026 workbook, and I did a search replace on VALUES, of 2025 to 2026, then 2024 to 2025. I am expecting the results to return columns headed Mar 2025, Apr 2025, May 2025, et cetera, up to Feb 2026, but not so. It has caused ten columns labelled Jan 2025 and then two labelled Jan 2026.

  • Whatever the format of my cells might be, viewing the value presents an Amerigocentric date value, viz. mm/dd/yyyy, and I am somewhat troubled by the opting of displaying a non-universal format in the cell. And it’s not very robust, because the software is getting confused between the date-value content of cells and the formatted appearance. I often have to re-type a cell date value in the international format yyyy-mm-dd to re-ascertain that it’s value is intended to be, for instance, the 1st of October not the 10th of January. This is not a permanent solution and it keeps biting me in the bum now and then.

I suspect that the software is fighting with itself over the above and I look forward to the day when I no longer have to wrestle with these nuances.

Thank you for your time and efforts.
JJHJ

#2

Really, the software is fighting with you. When you want a sequence of dates, start with your beginning date, then use a function to generate each following date. Internally all dates are stored just as big numbers, so search-and-replace on dates makes no real sense. Instead, make a copy of the old sheet with formula-controlled dates, then change the one, first date, and all the others will change.

I cannot speak to your formatting, but I have attached an example for US dates.
Basic Date Change Example.ods (22.9 KB)

#3

If I set my locale setting to South Africa, I see that the date acceptance patterns are as below

DateAcceptanceSA
DateAcceptanceSA734×241 8.1 KB

You could change the acceptance patterns if they disagree with you.

[Edit]
Wikipedia lists both formats for South Africa, List of date formats by country - Wikipedia

If you have a reference to the legal format in South Africa you could post a bug report if the acceptance patterns need to change, How to Report Bugs in LibreOffice - The Document Foundation Wiki

One format that is always accepted, and is explicitly accepted for South Africa, is Y-M-D; seriously consider using it and excluding all non ISO date formats

#4

Recent topic related to the South African locale, which changed in LibreOffice 24.8.

1 Like
#5

This is something you should check. If your cells contain/are recognizedbas text/strings all your date-formats are simply ignored. They apply only, if there is a number, where the matching date is shown. There may be aso the cause for your

To ensure proper work:

  • Check your date-acceptance patters. What is not included there will not be recognised and treated as string instead.
  • Set the format for your cells before input and remember this is only for output
  • You can check, what is read by changing the format of your cells to some numeric format. If cells are still shown as “dates” then, they are strings. This is safe, because:
  • Format does not change the value, only appearance
  • If you need something, wich ensures types, use a database. They (most of them) enforce the type a column is set to.

One of the other problems may be the history of your document. If you started with some template or copied data, this can import/include “foreign” settings and this can not simply be shown in your locale settings. (2 days ago I had to import a $10 as €9,28 as an example).

Not in your lifetime, unless you stop using spreadsheets. But see it positive: Maybe you never have to learn about different charsets (even if they are lurking in archives and csv-files)

#6

How do these values get into your workbook, if you don’t type them in? You do import them from text files, right?
In the text import dialog, you must not declare the desired result. That dialog is intended to describe the input.
The following settings import US-American data correctly into any spreadsheet regardless of locale settings, personal preferences or cell formatting:

US_Import
US_Import494×537 45.4 KB

The relevant option is the lanuguage “English (USA)” because the incoming data are meant to be interpreted in a US-English manner.
The check box “Detect special numbers” is obsolete. It should be always ON.

Delimiters, encoding etc. may still vary.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.