Dates Off When Copying

Discuss the spreadsheet application
Post Reply
3ME
Posts: 90
Joined: Wed Oct 08, 2008 6:12 am
Location: Chicago

Dates Off When Copying

Post by 3ME »

I am copying info from one OOo spreadsheet (converted from Excel) to another (original OOo) & every date that gets transferred to the new sheet is off by 4 years & 1 day. Ok... what gives?!! There are a lot of important dates to transfer!
OOo 4.1.7 on Mac OSX Catalina
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Dates Off When Copying

Post by acknak »

How are you "copying" the data?

Are the dates the result of formulas, or are they dates that people have entered?

Can you give us a completely specific example[*], or better, post a sample Excel sheet that copies into Calc incorrectly?

Have you checked what base date the Excel sheet is using? I believe some spreadsheets use a base date that is four years different from Calc.

[*}Something like:
A1: 2008-10-17 (shown as 10/17) copy/paste into Calc as A1: 2004-10-16 (shown as 10/16/04)
AOO4/LO5 • Linux • Fedora 23
3ME
Posts: 90
Joined: Wed Oct 08, 2008 6:12 am
Location: Chicago

Re: Dates Off When Copying

Post by 3ME »

acknak wrote:Have you checked what base date the Excel sheet is using? I believe some spreadsheets use a base date that is four years different from Calc.

[*}Something like:
A1: 2008-10-17 (shown as 10/17) copy/paste into Calc as A1: 2004-10-16 (shown as 10/16/04)
This example you've shown is exactly what's happening. Not sure how to check the base date in the old Excel file, but I've already converted it to OOo and made many changes. Is there a way to fix this in that converted file so when I'm copying to the 2nd one it doesn't do this?
OOo 4.1.7 on Mac OSX Catalina
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Dates Off When Copying

Post by acknak »

You can look at the options under Tools > Options > OO.org Calc > Calculate > Date. Maybe "1/1/1904" is the one you want.

Just be careful or you'll mask the problem and create a sheet that depends on some strange setting. I would worry that it might come back to bite me later on.

What I don't understand is why OOo didn't automatically make the adjustment when you converted the file. Maybe it's not possible to determine what base date was used, but I find that a little hard to believe.

I'm also not sure what will happen if you have dates in the same sheet that are based on different starting dates (some look right and some look wrong). Changing the base date will fix some and break the others.

You can safely transfer dates through a text format. If you copy a date "10/17/08" as text (e.g. paste as unformatted text in a Writer document, or paste into Notepad), then copy/paste the text date into the other sheet, then you will avoid the base date problem.

Unfortunately, I don't think there is a button you can press to make the problem go away.
AOO4/LO5 • Linux • Fedora 23
3ME
Posts: 90
Joined: Wed Oct 08, 2008 6:12 am
Location: Chicago

Re: Dates Off When Copying

Post by 3ME »

I started with 2.4 & recently updated to 3 when I upgraded to Mac 10.5.5. Forget what vers of OO this particular SS was stated in. Could that have had an effect? I'd rather change the dates in the transfer than have an issue later when adding after the trans... is that what you're recommending?
OOo 4.1.7 on Mac OSX Catalina
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dates Off When Copying

Post by Villeroy »

3ME wrote:I am copying info from one OOo spreadsheet (converted from Excel) to another (original OOo) & every date that gets transferred to the new sheet is off by 4 years & 1 day. Ok... what gives?!! There are a lot of important dates to transfer!
acknak wrote:You can look at the options under Tools > Options > OO.org Calc > Calculate > Date. Maybe "1/1/1904" is the one you want.
This is a per-document setting. Activate the first document, and have a look at the base date. Then activate the other one and look if the base is different. If they both use the same base the offset would be clearly a bug.
Anyway if it is a bug or the documents use different base dates for some reason, you can fix it like this:
Put 1463 in some cell (days between 1904-01-01 and 1899-12-30)
Copy that cell
Select the single cell range you want to adjust.
Edit>PasteSpecial [Insert key]
Check option "Subtract" and "Numbers", uncheck everything else.
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
3ME
Posts: 90
Joined: Wed Oct 08, 2008 6:12 am
Location: Chicago

Re: Dates Off When Copying

Post by 3ME »

Bingo! The base dates were different. In the doc I'm copying from, it's set to 01/01/1904 instead of the default 12/30/1899. But when I change it to the default, it changes all the dates in the cells. Is there a way to fix this?
OOo 4.1.7 on Mac OSX Catalina
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dates Off When Copying

Post by Villeroy »

Adjusting the values is one way to fix this. Adjusting the base date of the document where you paste into is the other way. All spreadsheets use numbers for dates and times. They represent dates and times by means of numbers in unit "Days" with a particular day as time zero.

Demonstrating dates in Calc, create a sequence of quarters:
-1.25
-1
-0.75
-0.5
-0.25
0
0.25
...
some hundred rows.
Change format to some date+time such as "YYYY-MM-DD HH:MM" and back to decimal number.
Change the base date.
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
3ME
Posts: 90
Joined: Wed Oct 08, 2008 6:12 am
Location: Chicago

Re: Dates Off When Copying

Post by 3ME »

Villeroy wrote:Adjusting the values is one way to fix this. Adjusting the base date of the document where you paste into is the other way. All spreadsheets use numbers for dates and times. They represent dates and times by means of numbers in unit "Days" with a particular day as time zero.

Demonstrating dates in Calc, create a sequence of quarters:
-1.25
-1
-0.75
-0.5
-0.25
0
0.25
...
some hundred rows.
Change format to some date+time such as "YYYY-MM-DD HH:MM" and back to decimal number.
Change the base date.
I've just noticed now in the new spreadsheet, the one that has the default base date, that the dates our -4 years off?! Not sure how this happened. I'm a little fuzzy on your instructions above on how to fix this... every time I play with the number formatting in the cells, it subtracts for years from the dates in the spreadsheet. I would appreciate further help here. Baby steps please. Thx!
OOo 4.1.7 on Mac OSX Catalina
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dates Off When Copying

Post by Villeroy »

Formatting should not change the values. That should be reported as a bug.
You copied 2008-01-01 from document_1.ods to document_2.ods and get 2004-12-30?

Activate document_2 and set Tools>Options...Calc>Calculation>Date from current 1899-12-30 to 1904-01-01.

Alternatively you can put 1463 in any cell (days between 1904-01-01 and 1899-12-30)
Copy that cell.
Select the date cells you want to adjust.
Edit>PasteSpecial [Insert key]
Check option "Subtract" and "Numbers", uncheck everything else and confim the dialog.
The latter keeps the old default base date and corrects the offset of the numeric values.
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
3ME
Posts: 90
Joined: Wed Oct 08, 2008 6:12 am
Location: Chicago

Re: Dates Off When Copying

Post by 3ME »

Villeroy wrote:Formatting should not change the values. That should be reported as a bug.
Wow... after reading through how to report a bug, I'm a little intimidated. Seems reporting a bug is not for beginners. Any suggestions?
OOo 4.1.7 on Mac OSX Catalina
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Dates Off When Copying

Post by acknak »

Did you look at [Tutorial] Reporting bugs or suggestions?

It's not as bad as it might seem. The most important thing is to have a test case that demonstrates the problem as clearly as possible, including a test spreadsheet document, if possible.

If you can register, write in the description box: when I load the attached sample sheet, type in 1234 in cell A1, then cell A2 shows "1/1/2008" when it should show "1/1/1904" -- something as simple as that -- then everything else in the report is just fuzz. It doesn't matter that much.

If you want to post your test here first, we can potentially help make sure it's clear. Someone here can even file it for you if you really don't want to deal with the issue tracker.
AOO4/LO5 • Linux • Fedora 23
3ME
Posts: 90
Joined: Wed Oct 08, 2008 6:12 am
Location: Chicago

Re: Dates Off When Copying

Post by 3ME »

Ok... the prob isn't happening in a new spreadsheet when I type dates into A1 & B1. So I've attached the actual doc with all the private info removed. Just dates. So in OOo 3.0, if you go to Format > Cells > Numbers then change the format, close & save the doc then reopen, the dates change - 4 yrs. Would appreciate some help here.
Attachments
Bug Test.ods
(11.62 KiB) Downloaded 379 times
OOo 4.1.7 on Mac OSX Catalina
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dates Off When Copying

Post by Villeroy »

Whatever OOo3 does with that document, I can not confirm this issue (changing values or zero-dates) with OOo2.4. But allow me another comment:
This is one of the very typical Excel sheets that died a silent death of formatting overkill. Sheet1 has no less than 67 different formattings in 189 ranges while having 33 values (including labels). I would not trust a single information derived from this mess.
http://user.services.openoffice.org/en/ ... lls#p49878
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
3ME
Posts: 90
Joined: Wed Oct 08, 2008 6:12 am
Location: Chicago

Re: Dates Off When Copying

Post by 3ME »

Villeroy wrote:Sheet1 has no less than 67 different formattings in 189 ranges while having 33 values (including labels). I would not trust a single information derived from this mess.
How to you see this info & what do I do to correct it?
OOo 4.1.7 on Mac OSX Catalina
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dates Off When Copying

Post by Villeroy »

Click the link in my previous reply. It is an "advertisement" for an add-on. However, I do not know if it works with OOo3 actually.
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
3ME
Posts: 90
Joined: Wed Oct 08, 2008 6:12 am
Location: Chicago

Re: Dates Off When Copying

Post by 3ME »

Villeroy wrote:Click the link in my previous reply. It is an "advertisement" for an add-on. However, I do not know if it works with OOo3 actually.
It won't install. Are these excessive formattings typically caused from the colors, headings, etc, that I'm using or could it be from all the trials & undoing and so forth? Being new to spreadsheets, I've experimented a lot in this particular file. Also, I'm copying & pasting all the rows & columns with titles from an xls file.

Can I fix this one? Or should I start fresh... and then, what should I NOT do?
OOo 4.1.7 on Mac OSX Catalina
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dates Off When Copying

Post by Villeroy »

It won't install.
Thank you for this important info.

Yes, when many users use the same spreadsheets to organize data by drag&drop and clipboard unexpected things may happen. I've seen many spreadsheets that grew over the time to thousands of cluttered rows until they could not be loaded by Excel anymore. All this is fine if you just need a small note pad for visual evidence. But things may go wrong when you try to evaluate, filter or search mixed number and text values, right-oriented numeric text, hidden values in merged cells, interchanged fields, phone numbers without leading zeros (plain numbers), formulas referring to moving targets, ...

Read about hierarchically organized styles.
Call the stylist (F11)
Save a copy of your document.
Hit Ctrl+A, Ctrl+Shift+Space in order to remove all hard formatting.
 Edit: In v3.0 they replaced Ctrl+Shift+Space with Ctrl+M. In any case it is "Format>Default" which removes all hard formatting to clean up the whole thing without changing values 
Re-format the document by means of styles and conditional formatting.

If formulas are involved, always insert new cells into the referred range before you paste unformatted values into the styled ranges. Hard formatting by point-and-click is still useful for the excptions to the rules.

I suspect my list keeper macrowon't run in OOo3 neither since it is written in Python.
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
3ME
Posts: 90
Joined: Wed Oct 08, 2008 6:12 am
Location: Chicago

Re: Dates Off When Copying

Post by 3ME »

Villeroy wrote:
It won't install.
Read about hierarchically organized styles.
Call the stylist (F11)
Save a copy of your document.
Hit Ctrl+A, Ctrl+Shift+Space in order to remove all hard formatting.
Re-format the document by means of styles and conditional formatting.

If formulas are involved, always insert new cells into the referred range before you paste unformatted values into the styled ranges. Hard formatting by point-and-click is still useful for the excptions to the rules.
Pardon my inexperience here... how do I know Ctrl+Shift+Space (I'm on a Mac BTW so I believe it's Command+Shift+Space) is working? I don't see a change. I did use the Styles and Formatting in the spreadsheet, but just for the headings in B1, C1, Z1 & AE1. I'm a little confused at this point as to whether this is good or bad. There are no formula cells... and least not done on purpose. This spreadsheet should just be text & dates.
OOo 4.1.7 on Mac OSX Catalina
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Dates Off When Copying

Post by acknak »

3ME wrote:
Villeroy wrote:...Hit Ctrl+A, Ctrl+Shift+Space in order to remove all hard formatting....
Pardon my inexperience here... how do I know Ctrl+Shift+Space (I'm on a Mac BTW so I believe it's Command+Shift+Space) is working? I don't see a change. ...
Note that this keyboard shortcut has changed in OOo3. Format > Default Formatting is now Ctrl+M across all OOo apps.

Ctrl+Shift+Space now inserts a non-breaking space, so unless you're editing the contents of a cell, it may well do nothing at all.
 Edit: PS: Oops, Ctrl+Shift+Space is "Select column" in Calc. 
AOO4/LO5 • Linux • Fedora 23
3ME
Posts: 90
Joined: Wed Oct 08, 2008 6:12 am
Location: Chicago

Calculating date sequences

Post by 3ME »

Thanx for that acknak!

Ok, that worked. I see, we're setting everything to default formatting. So all my dates turned in to numbers. What next?

I mean, I guess I would just change it to a date format... but I'm looking to steps here & advice on which date format, etc, so I don't get myself into trouble again. Can I go ahead & safely increase the font size & add the colored columns back in? I'm a little fuzzy on what got me into this mess in the 1st place. I still have info from the other spreadsheet that needs to be copied in... how do I handle that the right way?
OOo 4.1.7 on Mac OSX Catalina
3ME
Posts: 90
Joined: Wed Oct 08, 2008 6:12 am
Location: Chicago

Re: Dates Off When Copying

Post by 3ME »

You know what?... none of this solved the original concern anyway. Even after setting all cells to default formatting in this spreadsheet, if I Format>Cells>Numbers>Number, then change the format there, after closing then reopening... dates are -4 yrs. This is without selecting any cells. Can anybody tell me why this is happening?
OOo 4.1.7 on Mac OSX Catalina
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dates Off When Copying

Post by Villeroy »

This is what I tried to reproduce your issue:
- Installed v3.0
- loaded your "Bug Test.ods"
- Copied the formatted numbers (dates) from K4:K12 into a text editor
- Selected all cells
- Format>Default Format (they changed the shortcut from Ctrl+Shift+Space to Ctrl+M :roll: )
- Formatted K4:K12 as date
- Saved your document
- Quit the office
- Reloaded your document
Compared the values with those in my text editor and they remained the same.

K4:K12 is unformatted and with ISO-date formatting:

Code: Select all

26572	1972-09-30
26570	1972-09-28
0	1899-12-30
26570	1972-09-28
0	1899-12-30
26571	1972-09-29
26580	1972-10-08
26572	1972-09-30
26536	1972-08-25
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
3ME
Posts: 90
Joined: Wed Oct 08, 2008 6:12 am
Location: Chicago

Re: Dates Off When Copying

Post by 3ME »

Villeroy wrote:- Copied the formatted numbers (dates) from K4:K12 into a text editor
So if I want to start fresh & copy rows of a .xls file into a new, clean OO 3.0 spreadsheet, what's the best way to do this? Do I have to do it cell-by-cell ( an AWEFUL lot of work!) or can I copy rows somehow without copying any formatting that may corrupt my new OO spreadsheet? I tried copying an entire row & pasting into TextEdit, but it gives crazy results.
OOo 4.1.7 on Mac OSX Catalina
Post Reply