FJCC wrote: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 wrote: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 don't see Tools > Options
I find that cells will not convert to date cells when I format>cells>date
The result is if I enter the date 1/2/18, I get 1/2 (one half)/18.
Villeroy wrote: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.
Villeroy wrote:Villeroy wrote: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.
Users browsing this forum: No registered users and 16 guests