[Solved] Date Copies incorrectly between spreadsheets

[Roddy716]

I see that people have had similar problems in the past, but I wonder if this is a bug that still has not been corrected.
When I copy the dates from one spreadsheet to a new one the dates are changed. My spreadsheet has 557 lines. In the process of copying, on the first 451 lines the date is changed to one that is 4 years and 1 day earlier. On lines 452-557 the date is correct. If the format is changed to date before the paste is done, the same error occurs. So it is not a matter of formatting. Also, it is rather strange that lines 452-557 are correct. Incidentally pasting the dates into an Excel spreadsheets gives the correct dates, but that's really irrelevant.

Of course, I can copy the file using the Mac finder as a work around, but that's not the most direct way of doing it.

I'm using Open office 4.1.5 Mac Mojave 10.14.1.

Thanks

[RusselB]

Welcome to the Forums.
Can you attach/upload a small sample of your spreadsheet so that it can be analyzed?
See [Forum]How to attach a document here for more information.
The most common reason for this to happen, is that the base date for one spreadsheet is set differently from the base date for the other spreadsheet, however this does not fit with your statement that only some entries are being affected.

[Roddy716]

Attached are the two files with the dates copied from Correct Dates to Copy with incorrect dates. The first column was formatted beforehand with the date field, the second without reformatting. Makes no difference. As you can see, line 452 starts copying the correct date. Incidentally, doesn't make a difference if you do command-c command-v or use copy and paste from the edit menu.
Thanks for the quick response.

[FJCC]

The two files have different base dates. Select the menu Tools -> Options, expand the Calc list on the left, select Calculate and look in the Date section at the top right. One file is set to Dec 30, 1899 and the other to 1/1/1904. The "dates" that copied over without changing are actually text. You can tell by clicking on a cell and looking for a leading apostrophe in the formula bar.

[Villeroy]

Text mixed with numbers and different base dates is the maximum damage. I tried to fix both of your files.

In the file with base date 1899-12-30 having 2 columns of text I added a cell style "US Date" and applied that to the 2 columns. Then I converted the text to numbers.

In the 1904-01-01 file with one column of mixed text and numbers I added 1462 to all numbers (the difference between 1904-01-01 and 1899-12-30), set the base date to 1899-12-30, applied the same "US Date" style and converted the remaining text values to numbers.

Convert text to numbers:
Select the cells in question
Apply any number format in the correct locale matching the assumed number format of the text values. 1/15/2018 implies number format language English (USA). The locale setting is important. My default German locale would fail or would - even worse - yield wrong conversion results with month and day reversed.
Call menu:Edit>Find>Replace...
Under "More Options" check "Current selection only" and "Use regular expressions"
Search: .+
Replace: &
[Replace All]
This effectively re-enters all data into the selected cells using the number given format language.

[Roddy716]

The two files have different base dates. Select the menu Tools -> Options, expand the Calc list on the left, select Calculate and look in the Date section at the top right. One file is set to Dec 30, 1899 and the other to 1/1/1904. The "dates" that copied over without changing are actually text. You can tell by clicking on a cell and looking for a leading apostrophe in the formula bar.

1. Perhaps I'm missing something, but I don't see Tools > Options as you can see from the screen shot.
2. Shouldn't all new files open with set to the same date?
3. Why would some dates copy as text when the cells were formatted as date before they were pasted into the new spreadsheet?

[RusselB]

1) If I recall correctly, you need to find an entry called Preferences, since you are on a Mac.
I don't recall which menu item that's off of, and I don't own a Mac in order to be able to look it up.

2) That would make sense, but it's not always the case (obviously).

3) Numeric format of a cell will have no effect on the appearance of text that is pasted into the cell. Why you got text in the paste is a question I can't answer, especially when I have no idea as to where the information came from.

[Roddy716]

Text mixed with numbers and different base dates is the maximum damage. I tried to fix both of your files.

In the file with base date 1899-12-30 having 2 columns of text I added a cell style "US Date" and applied that to the 2 columns. Then I converted the text to numbers.

In the 1904-01-01 file with one column of mixed text and numbers I added 1462 to all numbers (the difference between 1904-01-01 and 1899-12-30), set the base date to 1899-12-30, applied the same "US Date" style and converted the remaining text values to numbers.

Convert text to numbers:
Select the cells in question
Apply any number format in the correct locale matching the assumed number format of the text values. 1/15/2018 implies number format language English (USA). The locale setting is important. My default German locale would fail or would - even worse - yield wrong conversion results with month and day reversed.
Call menu:Edit>Find>Replace...
Under "More Options" check "Current selection only" and "Use regular expressions"
Search: .+
Replace: &
[Replace All]
This effectively re-enters all data into the selected cells using the number given format language.

I find that cells will not convert to date cells when I format>cells>date even if I convert first to numbers and then to date. For some reason cells don't want to accept the date format.

[robleyd]

I don't see Tools > Options

For Mac - OpenOffice | Preferences

I find that cells will not convert to date cells when I format>cells>date

The cells will format, but if the content is defined as text it won't magically change. See [Tutorial] Ten concepts that every Calc user should know, particularly sections 1-3.

If you are new to Mac/AOO you may also find [Tutorial] Mac FAQ a useful resource.

[Roddy716]

1. During the night I realized why some cells were text and some were date fields. The first 451 lines were created in an Excel spreadsheet and then copied to the Open Office spreadsheet. Subsequent cells were created in Open Office. Copying dates from an Open Office spreadsheet to another Open Office spread sheet does not create this problem.
2. When I format a date cell on Open Office however I still don't seem to get a date field (as indicated by the apostrophe). The result is if I enter the date 1/2/18, I get 1/2 (one half)/18. Perhaps someone who knows the Mac version can help me with this. (Although somehow lines 452 and on do have the apostrophe).
3. For those who have Macs the date can be set with the preference menu as suggested. See upper right hand corner. Attached is a copy of that screen.

Thanks to all who have helped me with this.

[FJCC]

The problem with entering 1/2 and having it replaced with ½ is an annoying collision of two features. You can either go to Tools -> AutoCorrect Options and on the Replace tab remove the entries of 1/2, 1/4 and 3/4 or enter the dates with leading zeros, e.g. 01/02. It is not a Mac specific issue.

[Zizi64]

The result is if I enter the date 1/2/18, I get 1/2 (one half)/18.

Use the ISO format for entering Date-Time values:
2018-12-07 17:56:00

That will be recognized correctly.

[Villeroy]

1/2/18 yields second of January 2018 in the US locale and first of February for the rest of the world unless you type a space after the 2.

[RusselB]

When entering a date, my preference is to enter it like Jan 2
That will presume the current year, but will give January 2nd no matter what locale or display format is being used.
Just a suggestion to handle the problem of entering dates.

[Villeroy]

1/2/18 yields second of January 2018 in the US locale and first of February for the rest of the world unless you type a space after the 2.

Oh no. Right now I notice that AOO 4.1.6 interpretes 1/2/18 as ½/18. This is awful.

The vast majority of dates not starting with "1" can be entered as "23/" (this month's 23rd day) "23/12" (this year's 23rd November, "12/23" in US context). But all this has nothing to do with the original problem. Nobody typed all the useless text into the cells; and base date 1904-01-01 results from another spreadsheet application.

[Roddy716]

1/2/18 yields second of January 2018 in the US locale and first of February for the rest of the world unless you type a space after the 2.

Oh no. Right now I notice that AOO 4.1.6 interpretes 1/2/18 as ½/18. This is awful.

As is pointed out, this can be corrected with the auto correct function, so that's not so bad.

One last question< is there a date function that will indicate the day of the year number? ie Feb 5 2018 = 36.

I have found a work around by changing the date to text and subtracting 43100 (January 1. 2018 =43101. If I used 43101 Jan 1 would be day zero and Jan 2 day 1 which is not correct. It's rather cumbersome so a function of this type would be handy. Is there one?

An alternative obviously is the Days function, but a function would save some typing.

[robleyd]

You might find something useful that you can adapt in this post from a few days ago.

[Villeroy]

When converted to number, the text "12/08/2018" yields this exact date, no matter which base date is set. As long as it is text, there is nothing to add or add or subtract from it.

This is why I first added the difference of days to the numbers leaving the text values alone.
I did it like this: Copy a single sheet cell which has the correction value 1462, select the target cells, call Edit>PasteSpecial and check the "Add" or "Subtract" option. This will add or subtract the copied cell value 1462 to all numbers in the selected target range ignoring any text. It has to be a sheet cell. Copying the string "1462" won't do. Alternatively, you could use a formula like this one: =IF(ISNUMBER(A1);A1+1462;A1) which returns the corrected cell value or the literal text.

In order to switch correct dates with base date 1/1/1904 to the same dates with base date 12/30/1899 (which is also compatible with MS Excel) you add 1462 which increases the date by 1462 days. Today's 12/8/18 becomes 12/09/22. Then you adjust the base date to 12/30/1899. Now the corrected cell values represent the same dates as they did before (e.g. today's 12/8/18) and the mapping of numbers and dates is compatible with most of today's spreadsheet programs.
For the other way round you would subtract 1462 in order to decrease the numbers and then raise the base date from 12/30/1899 to 1/1/1904.

Finally, convert the literal strings (e.g. "12/08/2018") into numbers using the right number format locale and the above outlined replacement procedure. With US English locale "12/08/2018" and base date 12/30/1899 yields the numeric cell value 44904, with base date 1/1/1904 the cell value would be 43442.

As a rule of thumb, you must never accept any text dates in a spreadsheet. Numeric text will bite you sooner or later. The only exceptions to this rule are identifiers (part numbers), phone numbers, zip codes and the like. They should be entered as text. You never calculate with these numbers and leading "0" may have a special meaning.