by Villeroy » Sat Dec 08, 2018 2:50 pm
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.
Please,
edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x