Locales Sum Fuction and Date Calc

Misa · September 19, 2025, 3:49pm

Hello. Even thou i have (or i believe) my locales and my system right , i am trying to write dates on cells and it takes it M/D/Y while i have set it D/M/Y.
And when i use SUM fuction I take date result(or something like 5/20/99)

I use 25.2.6.2 version on gentoo system.
$ locale -a
C
C.utf8
el_GR.utf8
en_US.iso88591
en_US.utf8
POSIX

$ eselect locale list
Available targets for the LANG variable:
[1] C *
[2] C.utf8
[3] el_GR.utf8
[4] en_US.iso88591
[5] en_US.utf8
[6] POSIX
[ ] (free form)

Thanks

fpy · September 19, 2025, 4:51pm

would be interesting to know where is documented the impact of system locales on Calc date format ?

Format date in Calc - The Document Foundation Wiki

How to format the date?

Misa · September 19, 2025, 5:15pm

I just realized that this is happening only in one tab.

Villeroy · September 19, 2025, 4:57pm

How did you do that? By number format? In this case, you change the way how the cell value is displayed.
menu:Tools>Options>Languages>General, Locale = English(UK) affects how the entire office suite interprets numeric expressions (decimals, dates, times, booleans, currencies).

Misa · September 19, 2025, 5:10pm

This is how is configured in libreoffice.
But as far the day it doesn’t take the change.

Villeroy · September 19, 2025, 5:32pm

No formatting and no global setting will ever change a single value in your document. This is a feature!
In your screenshot, the left oriented dates are text, because 12/10/25 is 10th of December, whereas 13/10/25 is not a valid date because there is no 13th month.
Check the right oriented dates with
=MONTH(B2) => 1
DAY(B2) => 10
and with
=ISNUMBER(B2) => TRUE
=ISNUMBER(B13) => FALSE
The following formula can fix your dates:

=IF(ISNUMBER(A2);
    DATE(
        YEAR(A2);
        DAY(A2);
        MONTH(A2));
    );
    DATE(
        VALUE("20"&RIGHT(A2;2));
        VALUE(MID(A2;4;2));
        VALUE(LEFT(A2;2))
    )
)

Misa · September 19, 2025, 5:45pm

Thank you for your reply.
Sorry for my ignorance , where do i put this formula?
I am a begginer.

Villeroy · September 19, 2025, 5:49pm

Put the testing formulas anywhere. They demonstrate the issues with wrong values.
Put the “repair formula” anywhere in row #2. Then grab the cell handle in the bottom-right corner of the cell and drag it down.
Then copy the correct date cells and Edit>Paste-special [Ctrl+Shift+V] to B2. Paste values only.