Maybe not annoyances but ways of getting things done.

Replace dates with incremented year

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.

  1. Insert new column to right of dates column (new and blank column C)
  2. Apply this formula in new column C 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:
    =DATE(year(B7)+1,month(B7),day)B7))
  3. Select the cell C7 and drag down to select all rows in column C that you want to replace column B's dates
  4. Control-D (pastes the formula down the column). You should see the updated dates in column C.
  5. Select all of the column C cells you have changed, and copy to clipboard
  6. Put cursor in first date you want to change, that is, in cell B7
  7. Edit > Paste Special > Paste Special. Make sure All is not checked and Formula is not checked. Paste. OK the overwrite.
  8. You can delete column C.
  • Last modified: 2019.12.14 09:06
  • by smismi7