[Upstream] Calc shows wrong date in Excel created .xls

Bug #456100 reported by steffen on 2009-10-20
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
LibreOffice
Won't Fix
Low
OpenOffice
Invalid
Undecided
Unassigned
libreoffice (Ubuntu)
Undecided
Unassigned
openoffice.org (Ubuntu)
Undecided
Unassigned

Bug Description

Binary package hint: openoffice.org

1) lsb_release -rd
Description: Ubuntu 11.04
Release: 11.04

2) apt-cache policy libreoffice-calc
libreoffice-calc:
  Installed: 1:3.3.2-1ubuntu5
  Candidate: 1:3.3.2-1ubuntu5
  Version table:
 *** 1:3.3.2-1ubuntu5 0
        500 http://us.archive.ubuntu.com/ubuntu/ natty-updates/main i386 Packages
        100 /var/lib/dpkg/status
     1:3.3.2-1ubuntu4 0
        500 http://us.archive.ubuntu.com/ubuntu/ natty/main i386 Packages

3) What is expected to happen in LibreOffice Calc via the Terminal:

cd ~/Desktop && wget https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/456100/+attachment/857805/+files/09_1011_h3g_project_report.xls && localc -nologo 09_1011_h3g_project_report.xls

the Excel created file in cell A7 should be /22.

4) What happens instead is it is /21.

WORKAROUND: Use Gnumeric.

lsb_release -rd
Description: Ubuntu 11.04
Release: 11.04

apt-cache policy gnumeric
gnumeric:
  Installed: 1.10.13-1ubuntu1
  Candidate: 1.10.13-1ubuntu1
  Version table:
 *** 1.10.13-1ubuntu1 0
        500 http://us.archive.ubuntu.com/ubuntu/ natty/universe i386 Packages
        100 /var/lib/dpkg/status

ProblemType: Bug
Architecture: i386
DistroRelease: Ubuntu 9.04
Package: openoffice.org-core 1:3.0.1-9ubuntu3.1
ProcEnviron:
 PATH=(custom, no user)
 LANG=en_US.UTF-8
 SHELL=/bin/bash
SourcePackage: openoffice.org
Uname: Linux 2.6.28-15-generic i686

steffen (ooo) wrote :
steffen (ooo) wrote :

Thanks for this good product OpenOffice and good luck to find out the fault. Steffen

Chris Cheney (ccheney) wrote :

Can you please file this upstream bug at http://qa.openoffice.org/issue_handling/pre_submission.html and add the bug number back to this report?

Changed in openoffice.org (Ubuntu):
status: New → Incomplete
steffen (ooo) wrote :

Can we go on without that?

Chris Cheney (ccheney) on 2010-05-13
tags: added: jaunty
Chris Cheney (ccheney) on 2010-05-17
Changed in openoffice.org (Ubuntu):
importance: Undecided → Medium
status: Incomplete → Triaged
summary: - calculation error
+ [upstream] date wrong in excel file
summary: - [upstream] date wrong in excel file
+ date wrong in excel file
description: updated
Changed in libreoffice (Ubuntu):
importance: Undecided → Medium
status: New → Triaged
summary: - date wrong in excel file
+ Calc shows wrong date in Excel created .xls
tags: added: lo33
description: updated

Created attachment 47044
09_1011_h3g_project_report.xls

Downstream bug may be found at:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/456100

1) lsb_release -rd
Description: Ubuntu 11.04
Release: 11.04

2) apt-cache policy libreoffice-calc
libreoffice-calc:
  Installed: 1:3.3.2-1ubuntu5
  Candidate: 1:3.3.2-1ubuntu5
  Version table:
 *** 1:3.3.2-1ubuntu5 0
        500 http://us.archive.ubuntu.com/ubuntu/ natty-updates/main i386 Packages
        100 /var/lib/dpkg/status
     1:3.3.2-1ubuntu4 0
        500 http://us.archive.ubuntu.com/ubuntu/ natty/main i386 Packages

3) What is expected to happen in LibreOffice Calc via the Terminal:

cd ~/Desktop && wget https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/456100/+attachment/857805/+files/09_1011_h3g_project_report.xls && localc -nologo 09_1011_h3g_project_report.xls

the Excel created file in cell A7 should be /22.

4) What happens instead is it is /21.

summary: - Calc shows wrong date in Excel created .xls
+ [Upstream] Calc shows wrong date in Excel created .xls
Changed in df-libreoffice:
importance: Unknown → Low
status: Unknown → Confirmed

Integer 22 is the 21/01/1900.
Entering only the number 22 with a date format show the 22nd day since the origine date.

With Excel, you have 22/01/1900 cause for Excel 1900 is a leap year (false of corse).
It is an Excel bug, not a Calc bug.
So if you enter a date betxeen 1/1/1900 and 1/3/1900 you got a gap of 1 day.

GerardF, thanks for having a look at it. This resolution is confirmed via Microsoft's article "Excel 2000 incorrectly assumes that the year 1900 is a leap year": http://support.microsoft.com/kb/214326

steffen, when one only types in a cell 22, with a date format, it show the 22nd day since the original date. With Excel, one has 01/22/1900. However, Excel intentionally and incorrectly assumes 1900 is a leap year as noted in the article "Excel 2000 incorrectly assumes that the year 1900 is a leap year": http://support.microsoft.com/kb/214326

Hence, this is a bug in Excel, not LibreOffice. Feel free to report any other bugs you may find.

Changed in libreoffice (Ubuntu):
importance: Medium → Undecided
status: Triaged → Invalid
Changed in df-libreoffice:
status: Confirmed → Won't Fix
Changed in openoffice:
status: New → Invalid
Changed in openoffice.org (Ubuntu):
importance: Medium → Undecided
status: Triaged → Invalid
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.