Search code examples
datereplacelibreoffice-calc

How do I replace parts of a formatted date with another value in Libreoffice Calc?

I am using Libreoffiec 7.5 (currently the newest version) on Ubuntu 22.04.

I have a lot of cells with date values formatted with the German locale (if this matters), like

1.4.2023
4.4.2023

etc.

I want to replace the month, or the year, in all these cells with a different (valid) number. How do I do this? Plain search & replace does not work probably because it scans the actual value, which is a number, not the formatted date. Preferably without having to calculate beforehand how many days, or seconds, or whatever I would have to add to the date to end up with a shift of N years or months.

Plan B if the above is not possible: How do I tell Libreoffice quickly (by hotkey?) that it should autofill a specific year into cells which receive a date without year, so that when I type 1.4. (April 1st) it doesn't autofill the current year, but my previously specified year into the date? This should persist at least until closing LibreOffice, or until setting another year.

Solution

  • The short answer to both questions is No way.

    More details: regardless of the formatting of the cell with the date, the Find & Replace operation will not perform the required operation (you are right, this is due to the internal representation of the date as the number of days counted from the "base date", Tools - Options - LibreOffice Calc - Calculate - Date section); entering an incomplete date (day.month) always refers to the current year.

    Full answer.

    General recommendation for converting existing data: always convert in two stages - create an auxiliary range in which, using formulas or built-in Calc mechanisms, create the necessary data, after carefully checking the correctness of the conversion, use Copy and Paste Special - Values in place of the original data and clear auxiliary range.

    Let's assume that the dates you showed in your question as an example are in column A.

    Use the Year(), Month(), and Day() functions to get the corresponding parts of the original date, use the DATE() function to generate a new date with the necessary corrections.

    For example, to get a date one and a half years after the original, you can use the formula

    =DATE(YEAR(A2);MONTH(A2)+18;DAY(A2)) or

    =DATE(YEAR(A2)+1;MONTH(A2)+6;DAY(A2))

    Of course, instead of any of the parameters of the DATE() function, you can substitute a fixed value. For example, =DATE(1827;MONTH(A2);DAY(A2)) will return with the day and month from cell A2, but for 1827 (So you can implement the Plan B you described).

    In addition to the four functions described here, Calc contains a rather large set of functions for working with dates. You can see them in the Date section of the Function Wizard:

    Wizard

    or you can read a detailed description of each of these functions in the corresponding Help section

    Help