Issue 114020

Summary: wrong date calculation/display
Product: Calc Reporter: ohallot <>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: cno, elianedomingos, gbpacheco, issues, rainerbielefeld_ooo_qa
Version: OOO330m1Keywords: oooqa
Target Milestone: ---   
Hardware: Unknown   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Description Flags
sample with wrong date display
image of the display we get here
calc file with issue hilighted
Please see comments from rainerbielefeld Sat Aug 21 08:18:34 +0000 2010
spreadsheet with date formatting issue from scratch none

Description ohallot 2010-08-20 17:31:56 UTC
The following sample shows a bug in the date display

Cell A1 has formula =DATE(1907;02;24) and displays 23/2/1907 in mm-dd-aa
notation, or any other one.

If you type 24/2/1907 the display is 23/2/1907. Again, this is wrong.

Other dates less far from now are ok... see 2007 and 1958...
Comment 1 ohallot 2010-08-20 17:32:45 UTC
Created attachment 71223 [details]
sample with wrong date display
Comment 2 Rainer Bielefeld 2010-08-20 19:15:37 UTC
NOT reproducible with "Ooo-Dev 3.3.0 multilingual version German UI WIN XP:
[OOo330m1 (Build 9514)]"!

Looks like a DUP of Issue 113028

Please feel free to reopen this issue if you find evidence that this one is an
independent issue. 

I will finally close the issue after 10 days if we will not have got additional

*** This issue has been marked as a duplicate of 113028 ***
Comment 3 ohallot 2010-08-20 21:19:05 UTC
I am attaching another file where this issue raises:

In orange, the date range where the date displayed are wrong.

Note that the internal date serial number is not wrong. Look at formula in
column C. So it appears that it is a display calculation issue for dates.

This issue appears also in OO 3.2.1, Both in Linux and in Windows. Our locale is

Sorry to insist, but this is a severe misleading bug and affects confidence in
the tool.

Comment 4 ohallot 2010-08-20 21:19:53 UTC
Created attachment 71225 [details]
image of the display we get here
Comment 5 ohallot 2010-08-20 21:22:04 UTC
Created attachment 71226 [details]
calc file with issue hilighted
Comment 6 ohallot 2010-08-20 21:26:17 UTC
Reopening wit simpler samples of the bug.

Not sure the issue of the timezone are to be considered. We are not measuring
Comment 7 Rainer Bielefeld 2010-08-21 09:18:34 UTC
Where is the EVIDENCE showing that this issue is different from Issue 113028? I
can't see any! Your overhasty reopening only splits efforts to find the problem
unnecessarily, Issue 113028 would have been a much better place to collect

Please read  Issue 113028, try to reproduce the consequences, do the tests
recommended there and try to see relations to your problem.
Please be detailed in your descriptions! How can I see that the date is shown
wrong if you do not tell what your manual input was? from column 'C' I expect

Please contribute your dates.ods with new name, column headings and additional
rows and other (screenshot) information for different settings in 'Tools ->
Options -> Calc -> Date' as you see in my "dates_rainerbielefeld.ods". Before
you take screenshot, please copy / paste column 'C'. My columns 'E', 'H' only
are for my own information.

Please contribute information what date calculation settings you use!
Comment 8 Rainer Bielefeld 2010-08-21 09:19:47 UTC
Created attachment 71236 [details]
Please see comments from rainerbielefeld Sat Aug 21 08:18:34 +0000 2010
Comment 9 ohallot 2010-08-21 16:34:01 UTC
PLease find attached the spreadsheet with the results and images of my screen.

Linux Mandriva, 3.2.1, but also seen in 3.3.0,
M1 for windows (XP).
Comment 10 ohallot 2010-08-21 16:35:22 UTC
Created attachment 71241 [details]
Comment 11 Rainer Bielefeld 2010-08-21 19:07:23 UTC
you generally use 01-01-94 setting?
Can you reproduce the problem when you start a new document with default date
I see some strange differences in col 'B' between our documents in the
screenshots, and I don't have any Idea what the reason might be.

Somthing strange, else: generally I saw "-1445.00" in 'F2', but under some
curcumstances I saw "-2907,00", it seems when I save and close document with
default date calculation, modify to 1904-01-01 calculation with an other
document and reopen again.

That all seems worrying to mee, I will terminate for today and think about that

Opening YOUR "dates-ohallot.ods" with my "Reproducible with "Ooo-Dev 3.3.0
multilingual version English UI WIN XP: [OOo330m1 (Build 9514)]", with default
calculation, I see in rows 6 -7 "14/01/00", "15/01/01". Due your screenshots the
rechaneg from "14/..." to "15/..." is from rwo 19 to row 20. 
Comment 12 ohallot 2010-08-22 02:37:29 UTC
Hi Rainer

No I don't use (never used), anything other than the default for dates.

The file dates-ohallot.ods just above was created by selecting
dates-rainerbielefeld.ods first spreadsheet and copying & pasting into
dates-ohallot.ods first spreadsheet.

So, I did not paid enough attention to this: When pasting data into the new
spreadsheet, it looks like that the value inserted into the cell is actually the
value displayed. Since "VERDADEIRO" means "TRUE", the results inclumn C means
that column B does not match function DATE(YYYY;MM;DD), because DD=16.

But now please find attached the spreadsheet with manual input data and dates,
and with the default setting, and images I have here.

Data in column A was input manually.

Data in column B was typed manually as 16/01/<YEAR>, (1900,1901,1902,...)

Column C shows that dates in column B are equal with the results of the function
DATE(YYYY;MM;DD). Looks like the serial number for the date is OK.

Column D has this incredible result... =A2 with special formatting...

Comment 13 ohallot 2010-08-22 02:38:56 UTC
Created attachment 71244 [details]
spreadsheet with date formatting issue from scratch
Comment 14 ohallot 2010-08-22 02:42:23 UTC
I beg your pardon, column D is nothing, I was refering column H which is correct
(my mistake)
Comment 15 Joe Smith 2010-08-23 15:50:56 UTC
> Column D [H] has this incredible result... =A2 with special formatting...

@ohallot: Can you explain what is unexpected about the results in column H?

Here's what I see when I open your last attachment (ohallot-new-from-scratch.ods):

   [A]          [H]
   Simple No.   Date informaiton of column A
   1900         14. Mar. 1905
   1901         15. Mar. 1905
   1902         16. Mar. 1905
   1903         17. Mar. 1905
   1904         18. Mar. 1905

which looks correct to me: 1905-03-14 is serial number 1900 when 1899-12-30 is
the base date.
Comment 16 ohallot 2010-08-24 03:48:41 UTC
@jes: yes. My mistake.
Comment 17 ooo 2010-09-02 18:10:51 UTC
As seen on Olivier's machine this is timezone related, we'll just have to figure
out which, probably Sao Paulo.
Comment 18 Marcus 2017-05-20 11:33:17 UTC
Reset assigne to the default "".