Differences

This shows you the differences between two versions of the page.

Link to this comparison view

libreoffice_annoyances [2019.12.14 06:03]
smismi7 created
libreoffice_annoyances [2019.12.14 06:06] (current)
smismi7
Line 3: Line 3:
 ====Spreadsheet==== ====Spreadsheet====
 ===Replace dates with incremented year=== ===Replace dates with incremented year===
-Your spreadsheet has a column with dates, say 1/2/2019 and you want to replace all of them with the next year, e.g., 1/2/2020.   +Your spreadsheet has a column with dates, say B7 has 1/2/2019 and you want to replace the dates in the rest of the column with the next year, e.g., 2020.   
-  - Insert new column to right of dates column +  - Insert new column to right of dates column (new and blank column C) 
-  - Apply this formula in new column next to date you want to change.  Say column B row 7 has 1/2/2019.  Put this formula in column C row 7: <code>=DATE(year(B7)+1,month(B7),day)B7))</code>+  - Apply this formula in new column next to the first date you want to change.  Say column B row 7 has 1/2/2019.  Put this formula in column C row 7: <code>=DATE(year(B7)+1,month(B7),day)B7))</code>
   - Select the cell C7 and drag down to select all rows in column C that you want to replace column B's dates   - Select the cell C7 and drag down to select all rows in column C that you want to replace column B's dates
-  - Control-D (pastes the formula down the column)+  - Control-D (pastes the formula down the column). You should see the updated dates in column C. 
   - Select all of the column C cells you have changed, and copy to clipboard   - Select all of the column C cells you have changed, and copy to clipboard
   - Put cursor in first date you want to change, that is, in cell B7   - Put cursor in first date you want to change, that is, in cell B7
   - Edit > Paste Special > Paste Special.  Make sure All is not checked and Formula is not checked.  Paste.  OK the overwrite.    - Edit > Paste Special > Paste Special.  Make sure All is not checked and Formula is not checked.  Paste.  OK the overwrite. 
 +  - You can delete column C.
  • Last modified: 2019.12.14 06:06
  • by smismi7