Wrong year showing in date columns

Help with installation and general system troubleshooting questions concerning the office suite LibreOffice.
Post Reply
Baasha
Posts: 58
Joined: Fri Apr 11, 2008 9:32 pm

Wrong year showing in date columns

Post by Baasha »

Hi,
I am running LO 4.022 in Linux Ubuntu. On my many spreadsheets I have date columns. The default behaviour in the past has been: enter 24/8, column shows 24/8/13. Now I am finding that it is defaulting to 24/8/14, i.e. the year is always one more than it should be. I can't find any bugs reported on this so I am wondering if there is some new formatting setting required in version 4. Yes, my system clock is showing the correct time and date, and I am also aware of the setting in Options>Language Setting>Languages for date format codes but I don't think would affect what seems to be value error. Does anyone have any ideas?
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Wrong year showing in date columns

Post by Zizi64 »

The LibreOffice (and the Apache OpenOffice) uses three base date (it is similar to Excel):
1899. dec. 30. (Default in recent versions of the AOO/LO)
1900. jan. 01. (it was Default in StarCalc 1.0)
1904. jan. 01.

The actually selected base date means the 0.0000 floating point value. The date-time values are stored as floating point numbers: the integer part represents the date (relative to the base date) and the fraction part represents the time value.

Maybe it is checked a different base date in the new version of LO...

Check it in:
Tools - Options - LibreOffice Calc - Calculate - Date
Last edited by Zizi64 on Wed Aug 28, 2013 9:20 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Wrong year showing in date columns

Post by Villeroy »

Zizi64 wrote:The LibrewOffice (and the Apache OpenOffice) uses three base date (it is similar to Excel):
In either case 24/8 should enter this year's 24th August and not next year's.

Day 2013-08-24 entered with different base dates gives:
1899-12-30 <=> 41510
1900-01-01 <=> 41508 [-3]
1900-01-04 <=> 40048 [-1460]
And when switching base dates, any existing date values shift by 3 days or 1460 days but not by one year exactly.

Unless it is a well known issue, resetting the user profile might help.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Wrong year showing in date columns

Post by RoryOF »

I suggest you verify the system date in the BIOS on bootup, before the operating system takes over.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Baasha
Posts: 58
Joined: Fri Apr 11, 2008 9:32 pm

Re: Wrong year showing in date columns

Post by Baasha »

Thanks for the replies. @Rory I checked the bios time and it was 9 hours ahead of where it should be, but this didn't make any difference to LO's behaviour. It is my understanding that LO, and other programs, take their time from the operating system. In my case that is Ubuntu, which is set up to get the correct time from government time clocks on the internet. I assume that means that once the os takes over it overrides the time in the bios.

@Everyone else: I have rechecked the only 3 places I am aware of in Options for time related settings:
Options>General is set for interpret as years between 2000 and 2099
Options>Language Settings>Languages is set for Date Acceptance Patterns of D/M/Y, DD/MM/YY (as far as I know this is just a formatting setting)
LO>Calc>Calculate is set for the default date of 12/30/1899

So at the moment I still have dates being entered as 2014. This only started with LO vs 4 and it was a completely fresh install, not an upgrade. Should I report this as a bug? I haven't seen anyone else reporting this problem, so I am inclined to believe it is particular to my setup.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Wrong year showing in date columns

Post by Villeroy »

It's always safe to try a new profile. It may have tried to import a 3.x profile and failed.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Baasha
Posts: 58
Joined: Fri Apr 11, 2008 9:32 pm

Re: Wrong year showing in date columns

Post by Baasha »

This was a completely fresh install so there were no previous profiles to use. Nevertheless I did rename the profile and let LO create a new one, but the behaviour remains the same.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Wrong year showing in date columns

Post by RoryOF »

There have been cases where a new profile, on a clean install, is corrupt "out of the box", so it won't hurt to try deleting or renaming the existing profile

When you look at the clock displayed in the system status bar (or whatever it's called) does it definitely say 2013?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Baasha
Posts: 58
Joined: Fri Apr 11, 2008 9:32 pm

Re: Wrong year showing in date columns

Post by Baasha »

When you look at the clock displayed in the toolbar does it definitely say 2013?
I assume you mean my system toolbar, and yes that is 2013. I just did a little experiment and opened a writer document and inserted a date field. I shows 2013 also, so this problem seems to be confined to Calc.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Wrong year showing in date columns

Post by RoryOF »

LibreOffice is currently at v4.1.0; you could ask if anyone running that version on Ubuntu is getting a correct date in Calc; if so, it may be worth changing versions from your 4.0.22.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Wrong year showing in date columns

Post by RPG »

Hello

I have not the problem as described. As RoryOF I think install a newer version or install an older version.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Wrong year showing in date columns

Post by keme »

Baasha wrote:...I just did a little experiment and opened a writer document and inserted a date field. I shows 2013 also, so this problem seems to be confined to Calc.
Does it also affect Calc formulas, or just the plain date entry? What do you get from =TODAY() or =NOW()?
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Wrong year showing in date columns

Post by Villeroy »

And how about Writer tables?
Create a table (Ctrl+F12)
Right-click>Number Recognition = ON (checked)
Enter some date.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Baasha
Posts: 58
Joined: Fri Apr 11, 2008 9:32 pm

Re: Wrong year showing in date columns

Post by Baasha »

@ keme
=Today() gives 2013-08-27 which is correct
=Now() gives 1786-05-02 06:30 which is very weird

@ Villeroy

Entering numbers into a table with number recognition on just puts whatever I type into the cell. Unless I am misunderstanding what is supposed to happen? 27/8 gives 27/8, Aug 27 gives Aug 27, 13/8/27 gives 13/8/27, and 27/8/13 gives 27/8/13.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Wrong year showing in date columns

Post by Zizi64 »

=Now() gives 1786-05-02 06:30 which is very weird
Maybe you typed in (accidentally) the next formula:

Code: Select all

= - Now()
= minus Now()
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Wrong year showing in date columns

Post by keme »

I still don't see any clue to other causes than a bug in that version of the software. If that is the cause, installing a different version should fix it.

Does it assume next year regardless of whether the date is current, past or future? From your previous postings I assume that you entered the current date. See if it makes a difference when you enter yesterday's date and tomorrow's date (it could be an intentional change of behaviour).

Also, installing a diferent version is no great effort in most cases, so it is probably worth a try. I'm not sure how Ubuntu package management works ; if you want to try an older version, you may need to uninstall the current one first.
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Baasha
Posts: 58
Joined: Fri Apr 11, 2008 9:32 pm

Re: Wrong year showing in date columns

Post by Baasha »

@ zizl64
You're right, I must have been sloppy with the mouse (my cats will never forgive me). =Now() gives the correct date.

@ Keme
It doensn't matter what date I enter, it is always one year in advance. I will have to uninstall and go back to the previous version. In the meantime I will file a bug report, except that at the moment the bug page isn't working right either, arrggh.
Post Reply