Issue 113028 - Date reduced by 1 day when formatting changed to Finnish Date
Summary: Date reduced by 1 day when formatting changed to Finnish Date
Alias: None
Product: Calc
Classification: Application
Component: formatting (show other issues)
Version: OOo 3.1
Hardware: PC Windows Vista
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: needmoreinfo, oooqa
Depends on:
Reported: 2010-07-08 07:55 UTC by lisko
Modified: 2017-05-20 09:57 UTC (History)
6 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

18st century date problem (9.28 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-07-16 18:20 UTC, lisko
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description lisko 2010-07-08 07:55:01 UTC

My mother-in-law does a research in her family roots. She found this bug from
OpenOffice Spreadsheet / Calc application while feeding in 18st century dates
from some other source.

This is the data she put in, the first two sections are dates in Finnish format
and others are birthplaces and person names:

03.12.1755	15.12.1755	Saresmäki 		Isaac Pussinen 	Anna Kerätär 	Carin
07.09.1758	18.09.1758	Saresmäki 		Isaac Pussinen 	Anna Kerätär 	Isaac
12.04.1760	19.04.1760	Saaresmäk 		Isaac Pussinen 	Anna Kerätär 	Andreas
24.05.1763	20.06.1763	Saaresm. 	J. 53 No 6 Sivola	Isaac Pussinen 	Ann. Kerätär 	Anna
27.03.1764	18.06.1764	Saaresm. 	J. 53 No 6 Sivola	Isaac Pussinen 	Ann Kerätär 
Christina (nd.)
21.03.1766	11.04.1766	Saresm. 	J. 53 No 6 Sivola	Isaac Pussinen 	Anna Kerätär 	Anna
19.04.1773	01.07.1773	Saresmäki 		Isaac Pustinen 	Anna Kerätär 	Sara (conf.)

So everything is fine as long as the cells are not formatted. But when she
changed the format of the first columns to be dates, all those dates were
changed - 1 day, for example teh dates in first row changed to be 02.12.1755 and
14.12.1755. No matter if this same data is copy-pasted or hand-written, the same
thing happens.

She studied this a bit and discovered that the prolem is in dates 25.07.1790 and
26.07.1790. These both have same values:

date		value()	
23.07.1790	-39970	
24.07.1790	-39969	
25.07.1790	-39968	
25.07.1790	-39968	(input 26.7.1790 into the cell manually)
26.07.1790	-39967	(input 27.7.1790 into the cell manually)
28.07.1790	-39966	
29.07.1790	-39965	

Seems that it is impossible to write 26.07.1790 as it is being changed to
25.07.1790 and all dates since that are wrong. She did not find out if this
problem vanishes after some other year.

I'm not really sure about the exact version she has, it is 3.1 something with
Finnish language pack. It was the latest version that was available in May 2010
when we installed it to her new laptop.


Comment 1 Marcus 2010-07-08 08:45:53 UTC
In the meantime there should be the new version 3.2.1 available also for
Finnish, download from here. "", so
please install this and test again

This is not a P1 issue.
Comment 2 Joost Andrae 2010-07-08 16:58:02 UTC
I've tried the example with 1790 but I was not able to reproduce this in current
DEV300_m84 developer build (on Solaris x86). Maybe it's worth to check the basis
date entries in /tools/options/calc/calculate if they use the default of
12/30/1899. Perhaps this document is an import of an old Excel document
containing one of the old known Excel date bugs...
Comment 3 ooo 2010-07-09 16:58:14 UTC
May be related to issue 86094 and issue 102294, or issue 105864. Please check
with the latest release available. If the error still occurs please state your
time zone setting, that probably is Finland/Helsinki.
Comment 4 Rainer Bielefeld 2010-07-11 09:57:53 UTC
Please attach that "some other source"!
If it contains confidential you can send it tom be by E-Mail.
Or may be you can create a small sample document demonstration your problem?
You know about date settings in 'Tools -> Options -> OOo CALC -> Calculate?' Do
modifications here solve your problem?
Comment 5 lisko 2010-07-13 18:42:42 UTC
Hi all!

If you really need, I can attach the source, but it does not really matter - the
same problem occurs also if the dates are written to cells manually or for
example copy-pasted from notepad. She actually located the problematic date to
be 26-Jul-1790 by writing the dates manually to calc cells.

I'll ask her to check the date settings as well as to update to the latest
finnish version.
Comment 6 Rainer Bielefeld 2010-07-13 19:26:57 UTC
Please attach a (new document) sample with 3 columns like following example:
Date   Date in integer Number Format    Date as written text in text formatting
23.07.90    -39971                        23.07.90
24.07.90    -39970                        24.07.90
25.07.90    -39969                        25.07.90
26.07.90    -39968                        26.07.90
27.07.90    -39967                        27.07.90
28.07.90    -39966                        28.07.90
29.07.90    -39965                        29.07.90
30.07.90    -39964                        30.07.90
31.07.90    -39963                        31.07.90
01.08.90    -39962                        01.08.90
02.08.90    -39961                        02.08.90

Comment 7 lisko 2010-07-14 17:07:24 UTC

the same problem is visible in 3.2.1 Finnish version and with Finland/Helsinki
date settting.


Here is the result of your latest testing request:

When the dates you sent below are written to cells, everything goes fine since
Calc assumes 90 to mean year 1990 (Column A is text, column B is function
=int(a2) – int(a12) and column C is date in format

A        B     C
23.07.90 33077 23.07.90
24.07.90 33078 24.07.90
25.07.90 33079 25.07.90
26.07.90 33080 26.07.90
27.07.90 33081 27.07.90
28.07.90 33082 28.07.90
29.07.90 33083 29.07.90
30.07.90 33084 30.07.90
31.07.90 33085 31.07.90
01.08.90 33086 01.08.90
02.08.90 33087 02.08.90 

But when changed the year to 1790, the familiar issue is back (column A is text,
column B is date in format and column C is =int(B)):

A          B        C
23.07.1790 22.07.90 -39971
24.07.1790 23.07.90 -39970
25.07.1790 24.07.90 -39969
26.07.1790 25.07.90 -39968
27.07.1790 26.07.90 -39967
28.07.1790 28.07.90 -39966
29.07.1790 29.07.90 -39965
30.07.1790 30.07.90 -39964
31.07.1790 31.07.90 -39963
01.08.1790 01.08.90 -39962
02.07.1790 01.07.90 -39992 

So something is definetely wrong in July 1790. But is it OO or the PC, that's
the problem. Now every date seems to be --'d when formatting...
Comment 8 Rainer Bielefeld 2010-07-14 17:38:24 UTC
It seems to be difficult to apart between posted results and a sample document?
We need a sample document attached here!
If you contribute instructions how to reproduce the bug, please contribute a
step by step instruction containing every mouse click and every key press! Every
thing else only wasts my and your time. 
Comment 9 Rainer Bielefeld 2010-07-14 17:57:27 UTC
This one might have same roots like Issue 102810 (only a vague suspect)
Comment 10 lisko 2010-07-16 18:20:48 UTC
Created attachment 70661 [details]
18st century date problem
Comment 11 lisko 2010-07-16 18:26:51 UTC

sorry about my misunderstanding here. I had no intention to waste anyone's time
and document does not contain any confidential information as the problem can be
reproduced in her PC simply by adding the dates manually...

Now I have the example as .ods file. Unfortunately I cannot reproduce this
problem on my own PC, so this seems to be somehow related to that particular
Acer laptop of my mother-in-law.

So, the attached document id: 70661 contains the sample data formatted as text
(column A), as date in format (column), as date in format
(column E) and both dates as number (B and F). I see everything fine, but she
has both date column values --'d, so dates go one day backwards.
Comment 12 Rainer Bielefeld 2010-07-16 19:21:23 UTC
I believe we should close this issue, I do not have any hope that we will get
some useful bug information anytimes, and it seems that it's a problem with that
particular PC.

Any idea what information should be gathered with a sample document without any
problem (or do I misunderstand your "I can not reproduce"?). You even did not
mention whether that document has been created on the PC with the OOo where the
problem is visible.

We are still waiting for your results from reading issue 86094 and issue 102294,
or issue 105864.

My final attempt:
Please read our guidelines on
<> and  
<> before you file
further issues or post again here.Then please contribute a clear and simple step
by step instruction containing all observations (error
messages ...), EVERY key press and EVERY mouse click how to reproduce the
problem, and explain why you believe that your results are unexpected.
That means (for example): 
 do not write something like "I am not able to ..." or "change the format 
 to be dates, but
  6. left mouse click on …
     expected: …, color of … changes, … 
     actual: no …., color remains white, no …

Screenshots might help to explain the problem.

- always specify your OOo download source, version, localization,
  OS and Platform you used for your tests!
- attach a sample document SHOWING A PROBLEM with screensots (might be that
  shown values will be different on a different PC). I believe it would be 
  useful to attach a sample document showing the problem in one row with the 
  possibility to reproduce the problem with your step by step instrucition in a 
  secone row (all formatting prepared for the test).
- contribute complete information to your language settings in OOo and WIN!
- explain "she has both date column values --'d"
Comment 13 lisko 2010-07-17 09:57:36 UTC

let's forget this problem. That document was created on the problematic PC by
manually writing the dates to the spreadsheet. By "I cannot reproduce the
problem" I mean that when I open the document on my PC, all dates are fine and
as they should be. I read those previous problems you linked now and seems that
they are related.

Sorry about taking your time, I just thought that I could help to solve the
possible issue but obviously it seems that I am not capable of that. Or capable
of understanding your guidelines. I'll try to learn before I bother you or other
free SW developers again.
Comment 14 ooo 2010-07-19 13:17:38 UTC
It may be that her computer has the timezone set to somewhere in Finland, and
your computer has it set to somewhere else. Can you confirm this assumption?
Comment 15 lisko 2010-08-01 17:38:43 UTC
@er: We both have time zone set to (UTC+02:00) Helsinki, Kiev, Riika, Tallinna,
Sofia, Vilna and both have automatic daylight saving time change set to on.

@all: If it still helps you on debugging this, she can record a macro showing
the whole case; how the data is input into spreadsheet cells and how the date
goes wrong while changing the formatting of the cells etc. Would this kind of
macro be helpful?
Comment 16 Rainer Bielefeld 2010-08-01 22:39:09 UTC
We can try the macro, but I believe the best way would be that you try to make
the problem reproducible on your own PC. If you can't find the reason, I doubt
that we will find it.
Comment 17 Rainer Bielefeld 2010-08-20 19:15:37 UTC
*** Issue 114020 has been marked as a duplicate of this issue. ***
Comment 18 ohallot 2010-08-20 21:08:56 UTC
add cc
Comment 19 Rob Weir 2013-02-02 02:56:40 UTC
This Issue requires more information ('needmoreinfo'), but has not been updated
within the last year. Please provide feedback as requested and re-test with the the latest version of OpenOffice - the problem(s) may already be addressed. 

You can download Apache OpenOffice 3.4.1 from

Please report back the outcome of your testing, so this Issue may be closed or
progressed as necessary - otherwise the issue may be Resolved as Invalid in the
Comment 20 Edwin Sharp 2013-07-16 20:18:29 UTC
Insert dates
03.12.1755  15.12.1755
07.09.1758  18.09.1758
Format - Cells... - Numbers - Category - Date - Language - Finnish
Result is
12.3.1755  15.12.1755
9.7.1758   18.09.1758

Rev. 1502185 Debian
Comment 21 Rainer Bielefeld 2013-07-17 05:06:47 UTC
Still NOT reproducible with  "AOO 4.0.0-Dev – German UI / German locale  [AOO400m3(Build:9702)  -  Rev. 1502004]" on German German WIN7 Home Premium (64bit)", Common 4.0-dev User Profile

Some general hints to @all
Status CONFIRMED should be used if really all info required for fixing has been added to the Bug report. It is not sufficient that someone was able to reproduce a problem without any understanding.

That means that some conditions should be fulfilled:
a) Useful summary that describes the bug in telegram style in a way that it 
   can be distinguished from all other bugs
b) We have a description how the problem can be reproduced (for example that
   it is related to a particular AOO localization or whatever) so that
   other users have a reliable instruction how to reproduce the problem
c) all other Bugzilla pickers / fields are up to date. Here after Comment
   20 we still have wrong contents in Fields 
   - Keyword: Commenter did not tell what additional info still might 
    be required)
   - Operating System: Commenter thought that he had reproduced the problem
     with Linux, so OS should have been changed to "all"
   -  Latest Confirmation on: 	
   - Summary (was very meaningless), and for calendar issues generally
     localization info is required
   - Importance: It's not a trivial problem that for Finnish language 
     dates are not reliable

Similar Calendar dates often are rather sophisticated. May be a country becomes captured in a war by a neighbour country and now becomes a new time zone because because of political reasons or similar reasons might show unexpected results, because smart software knows about such events and considers it. Always some research should be done to exclude such roots of the observations (I did not until now here).


@Edwin Sharp:
I do not understand at all what you tested. What I read is that some date (03.12.1755) formatting changed from DD.MM.YYYY to MM.DD.YYYY after having changed language from whatever to Finnish? That's something completely different from reporter's original report "date reduced by 1 day when formatting changed to Finnish Date". Can you please explain?
This but probably is related to localization to AOO and OS, so that 
I hope I do not frustrate you with my pedantries ;-)

Can you reproduce your results with a 4.0.0-dev build as server installation with it's own user profile (please ask for assistance here if it's not clear for you what I want)?
Comment 22 Edwin Sharp 2013-07-17 05:36:33 UTC
(In reply to Rainer Bielefeld from comment #21)
> I hope I do not frustrate you with my pedantries ;-)
Not at all, such pedantries will frustrate only Steve Balmer :)

Original description of -1 day was not observed by me.
Comment 23 Edwin Sharp 2013-12-13 13:54:44 UTC
No info from author.
Comment 24 Markus 2014-09-15 10:22:06 UTC
In OpenOffice 4.1.0 I have exactly the samee problem. This is the current version installed:
AOO410m18(Build:9764)  -  Rev. 1589052
2014-04-22 11:43:54 (Di, 22 Apr 2014)

Any dates typed into the cell below 28.7.1790 appears a day lower. The dates after that appears correctly.
Enter to cell A1: 27.7.1790
Enter to cell A2: 28.7.1790
Enter to cell B1: =DAY(A1)
Enter to cell B2: =DAY(A2)

Result in 2x2 table with defaults:
26.07.90	27
28.07.90	28

Note that DAY() reveals the original entered day.