Dates changing (was: Input date)

[AnrDaemon]

Dates DO change in Calc on save-close-reopen. The shift is in days, not mere seconds taking to reopen a file.
I'm VERY angry.

http://img856.imageshack.us/img856/4989/ooodates1.th.png

http://img96.imageshack.us/img96/8955/ooodates2.th.png

It only confirmed for Open Document format. Saving as MS Excel '97 don't cause dates to shift.

[RoryOF]

Look at
http://www.oooforum.org/forum/viewtopic.phtml?t=105976
and perhaps also
http://neowiki.neooffice.org/index.php/ ... mp_in_Calc
for how to insert a fixed date.

[acknak]

Calc certainly should not be changing the values stored in the document. However, it may change the "zero" date that is used to display the dates: notice how all the dates are changed by the same amount?

Check the setting under Tools > Options > OOo Calc > Calculate > Date.

There may be some problem with your OOo settings profile that's causing the change; or maybe switching between different systems with different settings. [Tutorial] The OOo user profile

[AnrDaemon]

Indeed, the date base was set to 01.01.1900. I can accept an issue in displaying a date in such case.
But why it drifting on each consequent save?... That suggest that it ALTERING stored value.

[acknak]

I have no idea why the base date would change, although we see a consistent level of users with "mysterious" changes in their profile settings.

My suggestion from another thread is to actually check the base setting in your sheet:
=IF(DATEVALUE("1899-12-30")<>0;"Warning: base date has changed")

I played around a bit with changing the base date and could not get Calc to change the dates stored in the document. It only changed the display of the dates. I'm still not sure I understand what's happening in this situation.

[acknak]

{Mod note: Topic split from http://user.services.openoffice.org/en/ ... =9&t=55975)

[Villeroy]

I have no idea why the base date would change, although we see a consistent level of users with "mysterious" changes in their profile settings.

The base date is stored in each document.

[AnrDaemon]

Well, I don't think it's "mysterious changes in profile settings".
Or rather, they should not have such effect, unless they happen on the fly. Even then, I can't imagine an event, in which it would affect dates like it does.
I mean, I edit a file, save it, reopen. All dates are shifted.
I make a change in one date, save, close, reopen... ALL dates are shifted AGAIN... even these I didn't touched.

[Villeroy]

Try resetting your user profile and see if it helps. Close the office (mind any "quick-starter"), rename the profile folder, open the spreadsheets in question.

[AnrDaemon]

It already not resetting anymore, since I changed the date calculation base.
But I no longer trust ODF format in OO, until this bug is fixed.

[kingfisher]

If it's a bug, it's a new one. I have not experienced such behaviour over 6 years or so. MSFT uses a different base date.

[Hagar Delest]

I remember that topic (have not studied it in detail): [Solved] Calc changing my dates.

[AnrDaemon]

Sounds like the same issue, yes.

[vortexboy]

I'm having this same problem and it is so ****** annoying . I even though my entry was wrong, so I changed some of the dates with find & replace command...
But there's one thing I don't understand: I've been reading through this thread (not all of it though) and I don't have the setting to change the start date on the spreadsheet level - I only have it at the Global Level in Preferences (OpenOffice.org > Preferences > OpenOffice.org Calc > Calculate > Date.
I don't have a Calc menu option, so where is this start date setting function supposed to be at the spreadsheet level?

I use a Mac and have vers 3.41.

My dates cannot be changed as they represent set events & I use them to know when to take other action.

[F3K Total]

Hello,
you should first open any calc document to see the calc options.
R

[vortexboy]

Hello,
you should first open any calc document to see the calc options.
R

I have done so. Both documents are open. Where is this function please?

[F3K Total]

You should find it here:
Tools/Options/OpenOffice.org Calc/Calculate top-right

[vortexboy]

You should find it here:
Tools/Options/OpenOffice.org Calc/Calculate top-right

I have Tools, but no Options sub menu item.

[RoryOF]

Try under Preferences on a Mac.

[F3K Total]

it seems to be different on a MAC: http://forum.openoffice.org/en/forum/vi ... 919#p87919

[vortexboy]

Try under Preferences on a Mac.

I already have. But this is global prefs, not spreadsheet prefs.

[eremmel]

I've it reproducable with LO 3.6.4.4.
Note that the global defaults are mixed with the sheet's defaults.

1: I open new Calc doc and make sure that default date-format 1: (1899)
2: Add two dates to the sheet 30-12-1899 and 1-1-1904
3: Save the sheet as 'Default-Date.ods'
4: Change Calc default date to date-format 3: (1904)
5: Dates are now changing, but I re-edit those two values to 30-12-1899 and 1-1-1904
6: Save As this document as 'Mac-Date.ods'
7: Quit Calc
The latest date-format has changed the default also to format 3.
8: Open Calc with empty document and validate that date format is 3.
9: Open Mac-Date.ods Dates are OK
10: Open Default-Date.ods and see that dates have shifted! But when I open the Tools->Options->Calc->Calculate I see that date format 1 is checked!!!

It looks like it is not happening when I start Calc with default date format as default setting (The date format that you see when you open Calc with an empty open document), then both documents open with correct dates.

[vortexboy]

As eremmel said, the global prefs are mixed in with sheet prefs.
If you have one sheet open and change the prefs, it doesn't change the prefs on any other sheet. Those have to be set separately.
So, I had 2 sheets open with dates on them (hundreds).

I changed the start year date on one sheet & the dates changed but the dates didn't change in the 2nd sheet.
I then clicked on the 2nd sheet and did the same thing. The dates on the first sheet did not change either.

There's just a small matter of fixing the dates I manually changed and I will be back to where I was before this issue started.

Time to put the kettle on I think...

[eremmel]

So I think that on a Mac you should leave the default also on 1899. With that setting you can work with OO defaulted and Mac defaulted documents. Having an Mac-default will alter dates for a OO-defaulted document.

I opened the ods file and looked into 'content.xml'. With a Mac-defaulted document one finds there:

Code: Select all

<office:body><office:spreadsheet>
		<table:calculation-settings>
			<table:null-date table:date-value="1904-01-01"/>
		</table:calculation-settings>
		<table:table table:name="Sheet1" table:style-name="ta1">

The <table:null-date table:date-value="1899-12-30"/> is missing for 1899 type of date documents. When I add it by hand and update the document it is read correctly when Mac-date-format is the Calc default.
So the implicit handling of the default-date-format is wrong: reading/parsing of document according Calc's default. Then apply the implicit default due to ommision of <table:null-date...>.

Who gonna make an issue for this?

[vortexboy]

I can't believe this program has such an elementary fault. Surely, the open source crowd can do better!

[eremmel]

@vortexboy, did you ever developed some software, in global teams with a few 100 people and during many years?
The nice thing about open source you get your bugs for free, can fix them yourself or when reported with a good reproduction scenario they might be fixed quickly. If you pay for your software the latter two are differently (ever tried to get a fix for Mac or MS product as a consumer?)

[vortexboy]

Well, perhaps I *might* expect to find bugs and small minor defects in the open source scenario you state, but not one of this magnitude.
Surely, there must be a process, managed by the software publisher by which checks are made at each stage of any change in the software, so that things like this don't happen. The date function is a pretty significant and pivotal element in calculating software, because people depend on it for....errmmm...calculating.

Your answer is really a strange one.

Oh and paid software, especially from the big boys has almost never been bug/quirks free. Nowadays. the public is sold beta-level software and the public carry out the quality control, checking and bug reporting for the company - nice way to treat your customers.

I know we don't live in a perfect world, but software companies have never been honest about their products & sell us incomplete and troubled products. Therefore, perhaps their products should always be sold with a warning on the package, like E&OE?

[Villeroy]

Whatever I try, I can not reproduce the 1462 days problem. Somehow it seems to happen on the Mac only and I have no Mac at hand. Unless anybody finds some routine to reveal this problem so a developer can see it, there will be no chance that this will ever be fixed.

[eremmel]

I tested under Windows, the following:
Open Calc and set default to:

Settings to test issue with 1462 days.

Open the three attached documents and you should see the results:

The three files with different zero date.

See next post for the three documents.
The document 'BaseDate1899-OK.ods' is patched by hand. I added '<table:calculation-settings><table:null-date table:date-value="1899-12-30"/></table:calculation-settings>' to content.xml

[eremmel]

Here are the three documents that are part of the previous post.

BaseDate1899.ods

Document with default zero-date with year 1899.

(8.73 KiB) Downloaded 167 times

BaseDate1899-OK.ods

Document with default zero-date with year 1899. This setting is explicitly patched.

(8.78 KiB) Downloaded 153 times

BaseDate1904.ods

Document with zero-date with year 1904.

(8.84 KiB) Downloaded 159 times

The first document loads only OK when the default for zero-date is the first entry.

It would be nice if this issue can be confirmed for OO 3.3.x or 3.4.x and for Mac, Linux and Windows.
I tested it only on LO3.6.4.4 Windows.