[Solved] Calc changing my dates

[jackofall]

I have a spreadsheet (actually several of them) that Calc has started to change the dates every time I save it. It only changes the cells that I modify, but it always changes them to be 4 years and 1 day earlier, just as soon as I save the file and reopen it. You can see from my two screenshots that the first shows the dates as I entered them and the second shows the dates right after I re-opened the file. If I open it a second time without modifying the dates, it won't adjsut them any further. However, if I manually change them back to what they were supposed to be, they will be changed the next time I open the file.
If I start a brand new file, it won't do this. However, I have a lot of spreadsheets that I now cannot trust the date on because it changes some of them (how am I to know which to trust now). Some of these spreadsheets date back 4 or 5 years since their creation.
I checked the date behavior in the Tools>Options>Calc>Calculate window and they are all set to 12/30/1899 'default'
I am running OO 3.4 (AOO340m1 Build9590 - Rev 1327774) on Windows 7.

screenshot before closing the file

Screenshot after opening the file

Swimming Date issues.ods

test file that replicates the error

(10.7 KiB) Downloaded 244 times

[thomasjk]

I don't see any changes at all when I open and close the file you posted. I even added a row and nothing changed when saved and reopened. I'm running LO 3.5..5.3 on WIN 7.

[RoryOF]

Might this be related to the following, extracted from the Help file?

Code: Select all   Expand viewCollapse view

Date base for day zero
Dates are calculated as offsets from a starting day zero. You can set the day zero to be one of the following:

'30/12/1899'   (default)
'01/01/1900'   (used in former StarCalc 1.0)
'01/01/1904'   (used in Apple software)

Choose Tools - Options - OpenOffice.org Calc - Calculate to select the date base.

When you copy and paste cells containing date values between different spreadsheets, both spreadsheet documents must be set to the same date base. If date bases differ, the displayed date values will change!


Find this in the Help file under Date and Time Functions.

[Villeroy]

I can't reproduce the date change neither. I always see the "before" dates.
The "after" dates are 1462 days apart which is the same difference as between 1899-12-30 (default) and 1904-01-01(Apple).

[MrProgrammer]

Read section 3. Dates in cells in Ten concepts that every Calc user should know. Determine if the number in the cell is changing when you reopen the spreadsheet or if the number remains the same and the displayed date is changing. You can view the number in the cell by temporarily removing the date formatting (Format > Default formatting) and then restoring it (Edit > Undo). Note that option Calc > Calculate > Date is spreadsheet-specific, not global for all spreadsheets. Determine if this option is changing when the spreadsheet is reopened. I would think that if the displayed date changes, either:

• the number in the cell changed (perhaps the result of a formula?), or
• the Calc > Calculate > Date option for that spreadsheet changed, or
• your user profile is bad (better chance of this if you installed 3.4 without creating a new one).

It only changes the cells that I modify, but it always changes them to be 4 years and 1 day earlier, …

If you're modifying the cells by copying a date (not text) from another spreadsheet, keep in mind that it's copying the number, and not the Calc > Calculate > Date option. So if you want the meaning of the number to remain unchanged, the two spreadsheets should be using the same Calc > Calculate > Date option. If you're modifying the cells by copying dates from a webpage, you're copying HTML, which is potentially a lot more complicated than it seems because formatting and possibly other properties are carried within the HTML.

There are some similar topics for date changing problems:
Dates off when moving between OOo 3.2 and 3.3
Calc changing dates after save
Dates Off When Copying

[jackofall]

I read the suggested help topics and other postings before my posting, they didn't help (though I did learn about new stuff).
I have confirmed that the calculation setting for the date is unchanged between edits of the spreadsheet.
As suggested, I entered a new name (Elvis with the correct 1/8/35 birthday) and added a new column. I manually entered all dates in accordance with the original dates. I copied this date list to a new column, converted to a number, saved the spreadsheet, and closed it.
When I re-opened it, the dates were all adjusted 4 years and 1 day again. I copied the adjusted dates to a new column and converted them to a number. You can see that they are not the same as the first time I did that. Below are the screenshot of what it looks like on my computer and the as-of-right-now current test spreadsheet.

Screenshot of what it looks like on my screen.

Swimming Date issues.ods

File with recent modifications

(13.18 KiB) Downloaded 172 times

[jackofall]

Ooops. Wrong screenshot. Here is the more current one...

[gerard24]

your user profile is bad (better chance of this if you installed 3.4 without creating a new one).

I think your user profile is corrupted. Have a try with a new one (renaming existing one).

[MrProgrammer]

Possibly Calc wrongly changes dates or When saving a spreadsheet the cells formatted as date are modified or Date cell decremented by 1462 when file opened or ISO date is different with different versions of Calc?

[jackofall]

I believe resetting my user profile is preventing me from automatically changing dates in the future when I opened files. I will have to test some more, but I think my user profile is no longer further corrupting my spreadsheets.
However, this is clearly a BIG problem if a computer crash can corrupt a user profile that then will corrupt previously unaffected files when they are opened at a later date.
I would not pretend to know what is going on in the user profile that can cause this, but it should not be a possible outcome.

I have been a big fan of OO until this happened. I'm not sure I can be going forward. I will never be confident that the last power outage didn't corrupt my user profile and make it so that I am about to corrupt my critical file the next time I open it. Very disappointing.

[acknak]

The profile problem is really quite serious but it's very hard to fix because there's no way to consistently trigger or reproduce it.

Anyway, this is one reason I prefer to keep important date information as text--Calc can't go changing it.

I shouldn't say, because I'm not 100% sure how this works, but I'm not sure there is really any corruption in your file data, at least not from the profile glitch directly. I believe the only situation where you have a problem is when you have some dates entered under one setting and others entered under the alternative setting.

It's easy enoght to at least check whether something bad has happened by including a formula, something like
=IF(DATEVALUE("1899-12-30")<>0;"Warning: base date has changed")

You could put some conditional formatting to make it show up in red or something.

[MrProgrammer]

your user profile is bad (better chance of this if you installed 3.4 without creating a new one).

If you installed 3.4 but kept the user profile from a previous installation, that was probably the cause of your trouble, not a "power outage".

[jackofall]

Is that supposed to make me feel better about the problem?
Either a unexpected outage causes a corruption due to an unexpected circumstance happening, or a totally expected action doing exactly what it is supposed to do causes the problem.
I don't feel good about either one.

Based on my testing with this file, I am sure that 'some' of my dates were reset to a previous day, but not all. None of them showed any date format difference on this file (that I could see), so I don't think they will show up as 'different' on a now corrupt file.

Would it help to have a corrupt user profile to fix the problem?

[kingfisher]

The only time I have had to renew the profile folder was when changing from a 2.x version to a 3.x version. It seems that installing the Apache version requires a new profile folder.

It is good practice to backup your documents AND the profile folder. I do that at the end of each day. I have at least two spreadsheet documents which contain numerous date entries, both as data and as the result of formulas. The dates stay the same. I don't know why dates are changing (if that is the case) in your documents. You could convert them to text and/or insert check formulas as acknak suggests.

Errors seem to be cropping up even with software that has previously behaved without a problem. I have tired, for example, of Adobe Reader asking again for acceptance of licence terms every so often. The only way to stop that recurring (temporarily) is to delete the existing profile folder, accept the licence terms and set preferences all over again.

[jackofall]

This is happening again on a brand new computer Windows 7 that has only had LO 3.6 installed on it. Switches my dates back 4 years 2 days every time I modify the spreadsheet and save it.
All the explanations from before of "it should" act like this or that, or isn't really doing what you describe, though that's how it looks don't hold up.
There is a major flaw endemic to your base code that needs to be fixed.

[kingfisher]

Whose base code? Be wary of LibreOffice 3.6.

[jackofall]

The shared code that both Libreoffice and OpenOffice are built on. This originally happened on a 3.4 OO on Windows 7. Now with a different spreadsheet on a different computer with a different software build.

[kingfisher]

The two codes have diverged. 'We' don't own either; we are, like you, only users of the software, not developers.

To get the code changed you have to file a bug report. The site for that is probably on the LibreOffice website.

Have you looked at the threads mentioned earlier dealing with dates changing? My spreadsheets contain hundreds of dates and not one has ever been changed except by me.

The only thing I can think of (without looking at those threads) is that the changes occurred when you saved .xls documents as .ods documents. MS uses a different base date from OO. (AOO and LO almost certainly use the same base date as OO.) The only .xls documents I have contain dates entered as text.

[Villeroy]

MS uses a different base date from OO. (AOO and LO almost certainly use the same base date as OO.) The only .xls documents I have contain dates entered as text.

Calc's base date is compatible with Excel's. Starting with 1900-03-01 (day 61) all numbers are mapped to the same dates. The 60 days before that day are wrong in Excel. Any dates before 1900-01-01 are unknown to Excel.

1899-12-30 => 0 (no date in Excel)
1899-12-31 => 1 (no date in Excel)
1900-01-01 => 2 (1 in Excel)
...
1900-02-28 => 60 (59 in Excel)
1900-02-29 => not a date (60 in Excel)
1900-03-01 => 61 in both
...

[kingfisher]

Thanks. The problem lies elsewhere then.

[jackofall]

It has happened to 2 separate spreadsheets on 2 separate computers running 2 separate versions of OO. I never save them as .xls, I avoid microsoft at all costs. It can ONLY be a bug with OO/LO (and yes, since I got the same error on two separate systems with 2 separate programs installed - each system never had the other one installed, and that both programs branched off the same code at some previous point in their development, it would be implausible to believe it is not based on some shared code).
I have used numerous computers to access these spreadsheets over the years, but they were created as .ods files and kept as that their whole life.
I will file a bug report, as this is infuriating. It has cost me a good 6 hours to figure out what was going on, where it was going on, and find a backup that was not corrupted and undo the changes. Then another 4 hours to deal with the VERY helpful folks on this forum to sort out how to stop if from happening again (only to have it happen again).
Sorry to direct any of that frustration at you. You guys have been wonderful with helping me.

[Johnnydon]

OK solution for me was way easier than I imagined after reading most of this stuff here.

The best tip was from the guy who said he went to the help page and checked the "calculate" date day from the original xls. to the new/other one which received the copied data.
So here is what he said in a Step by Step instructional that hopefully won't fail unless you're having too much of a good time to notice.

btw - all my calc doc.s are in xls.

1. First goto your source xls. document and

Goto
Tools -->Options ---> OpenOffice.org Calc (it's the 4th line down on OO version 3.3 that I use) ----> expand this then

click/goto ---->Calculate

In the top right is the date options

Make a note of which date is selected (mine shows 3 options and in this case it was set to the 1904 option, which explains old m8's 4yrs and one day scenario which was exactly what I had)

2. Secondly repeat the above steps for your new xls. document

3. Finally change the date here to match the date in the source xls.

You're done.

Hopefully your solution is as easy as this

[calc-learner]

Additional Problem: I was having a similar issue that brought me to this thread. The problem I was having was that anytime we updated the date in our database, it would go backward 1 day. I.e. if I typed Jan 1, 2017, it would get changed to Dec 31, 2016 once it saved.

Solution: The server where the database was on was not set to the same timezone in Windows. And so this caused the database to recalculate the date based on the servers time zone. The fix was to simply set the time zones to the same for the server and the computers accessing it.

Cheers!