[Solved] Calc changing my dates
[Solved] Calc changing my dates
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.
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.
Last edited by jackofall on Fri Aug 10, 2012 2:56 am, edited 2 times in total.
LibreOffice 3.6.1.2 on Windows 7
Re: Calc changing my dates
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.
Tom K.
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
Re: Calc changing my dates
Might this be related to the following, extracted from the Help file?
Find this in the Help file under Date and Time Functions.
Code: Select all
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!
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Calc changing my dates
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).
The "after" dates are 1462 days apart which is the same difference as between 1899-12-30 (default) and 1904-01-01(Apple).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- MrProgrammer
- Moderator
- Posts: 4905
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Calc changing my dates
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:
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
- 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).
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.jackofall wrote:It only changes the cells that I modify, but it always changes them to be 4 years and 1 day earlier, …
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
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Calc changing my dates
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.
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.
LibreOffice 3.6.1.2 on Windows 7
Re: Calc changing my dates
Ooops. Wrong screenshot. Here is the more current one...
LibreOffice 3.6.1.2 on Windows 7
Re: Calc changing my dates
I think your user profile is corrupted. Have a try with a new one (renaming existing one).MrProgrammer wrote:your user profile is bad (better chance of this if you installed 3.4 without creating a new one).
LibreOffice 6.4.5 on Windows 10
- MrProgrammer
- Moderator
- Posts: 4905
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Calc changing my dates
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?
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Calc changing my dates
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.
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.
LibreOffice 3.6.1.2 on Windows 7
Re: Calc changing my dates
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.
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.
AOO4/LO5 • Linux • Fedora 23
- MrProgrammer
- Moderator
- Posts: 4905
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Calc changing my dates
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".MrProgrammer wrote:your user profile is bad (better chance of this if you installed 3.4 without creating a new one).
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Calc changing my dates
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?
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?
LibreOffice 3.6.1.2 on Windows 7
- kingfisher
- Volunteer
- Posts: 2123
- Joined: Tue Nov 20, 2007 10:53 am
Re: [Solved] Calc changing my dates
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.
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.
Apache OpenOffice 4.1.9 on Linux
Re: Calc changing my dates
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.
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.
LibreOffice 3.6.1.2 on Windows 7
- kingfisher
- Volunteer
- Posts: 2123
- Joined: Tue Nov 20, 2007 10:53 am
Re: [Solved] Calc changing my dates
Whose base code? Be wary of LibreOffice 3.6.
Apache OpenOffice 4.1.9 on Linux
Re: [Solved] Calc changing my dates
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.
LibreOffice 3.6.1.2 on Windows 7
- kingfisher
- Volunteer
- Posts: 2123
- Joined: Tue Nov 20, 2007 10:53 am
Re: [Solved] Calc changing my dates
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.
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.
Apache OpenOffice 4.1.9 on Linux
Re: [Solved] Calc changing my dates
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.kingfisher wrote: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.
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
...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- kingfisher
- Volunteer
- Posts: 2123
- Joined: Tue Nov 20, 2007 10:53 am
Re: [Solved] Calc changing my dates
Thanks. The problem lies elsewhere then.
Apache OpenOffice 4.1.9 on Linux
Re: [Solved] Calc changing my dates
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.
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.
LibreOffice 3.6.1.2 on Windows 7
Re: [Solved] Calc changing my dates
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
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
Open Office 3.3 on Windows Vista
-
- Posts: 40
- Joined: Fri Oct 10, 2014 10:14 am
Re: [Solved] Calc changing my dates
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!
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!
Apache OpenOffice 4.1.2 Windows 10 Enterprise