Wrong year showing in date columns

Help with installation and general system troubleshooting questions concerning the office suite LibreOffice.

Wrong year showing in date columns

Postby Baasha » Sat Aug 24, 2013 10:34 pm

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?
Baasha
 
Posts: 51
Joined: Fri Apr 11, 2008 9:32 pm

Re: Wrong year showing in date columns

Postby Zizi64 » Sun Aug 25, 2013 7:35 am

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8328
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Wrong year showing in date columns

Postby Villeroy » Sun Aug 25, 2013 9:53 am

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27206
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Wrong year showing in date columns

Postby RoryOF » Sun Aug 25, 2013 10:00 am

I suggest you verify the system date in the BIOS on bootup, before the operating system takes over.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29560
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Wrong year showing in date columns

Postby Baasha » Mon Aug 26, 2013 8:53 pm

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.
Baasha
 
Posts: 51
Joined: Fri Apr 11, 2008 9:32 pm

Re: Wrong year showing in date columns

Postby Villeroy » Mon Aug 26, 2013 9:15 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27206
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Wrong year showing in date columns

Postby Baasha » Mon Aug 26, 2013 9:32 pm

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.
Baasha
 
Posts: 51
Joined: Fri Apr 11, 2008 9:32 pm

Re: Wrong year showing in date columns

Postby RoryOF » Mon Aug 26, 2013 9:35 pm

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.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29560
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Wrong year showing in date columns

Postby Baasha » Mon Aug 26, 2013 9:48 pm

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.
Baasha
 
Posts: 51
Joined: Fri Apr 11, 2008 9:32 pm

Re: Wrong year showing in date columns

Postby RoryOF » Mon Aug 26, 2013 11:06 pm

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.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29560
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Wrong year showing in date columns

Postby RPG » Tue Aug 27, 2013 10:14 am

Hello

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

Romke
LibreOffice 6.2.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2171
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Wrong year showing in date columns

Postby keme » Tue Aug 27, 2013 10:41 am

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 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3256
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Wrong year showing in date columns

Postby Villeroy » Tue Aug 27, 2013 11:10 am

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27206
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Wrong year showing in date columns

Postby Baasha » Wed Aug 28, 2013 2:46 am

@ 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.
Baasha
 
Posts: 51
Joined: Fri Apr 11, 2008 9:32 pm

Re: Wrong year showing in date columns

Postby Zizi64 » Wed Aug 28, 2013 7:02 am

=Now() gives 1786-05-02 06:30 which is very weird


Maybe you typed in (accidentally) the next formula:
Code: Select all   Expand viewCollapse view
= - Now()

= minus Now()
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8328
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Wrong year showing in date columns

Postby keme » Wed Aug 28, 2013 8:41 am

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 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3256
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Wrong year showing in date columns

Postby Baasha » Thu Aug 29, 2013 1:12 am

@ 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.
Baasha
 
Posts: 51
Joined: Fri Apr 11, 2008 9:32 pm


Return to LibreOffice

Who is online

Users browsing this forum: No registered users and 1 guest