need to bulk change year in date column

Any reason for posting the question TWICE in a span of six minutes?

This is a user-to-user community,populated by users of the software and hardware under discussion. The people posting here are not 'on the clock', and participate on their own time.

Do the Date column that needs to be changed contain the full date, or only the year?

Assuming it's the whole date and time value (with the time part not displayed), does each date need to be changes to the same day of the same month one year later?

Assuming that to be the case, the following should do the job:

For the example, I'll assume the dates are in column B.

• Click on any cell in column B to select that cell.
• Press option-right arrow to insert a new column to the right of column B. (It will become column C)

• Click the cell in column C adjacent to the first date in column B (for the example, that is C2)
• Type = to open the Formula Editor, and enter this formula:

EDATE(B2,12)

• Click the green checkmark to confirm the formula and close the editor.

• With C2 still selected, press command-C to copy the cell and its formula.
• With C2 still selected, scroll down to the last cell in column C.
• Press shift and click on the last celll in column C to add it and all cells between it to the selection.
• Press command-V to paste the formula into all cells in column C. You should see the results immediately.
• With all cells in column C still selected* Press command-C to copy.
• Go to the Edit menu and choose Paste formula results.

• Option 1:
  • With column C still selected, open the format inspector, choose cell, and set the data format to match the format used in the original column B.
  • Delete Column B, making C the new column B, then label this column with the same label as was in the previous column B.

OR

• Option 2:
  • Copy the cells in column S again (this time copying the formula results that you just pasted in).
  • Click on cell B2, and Paste.
  • With column B selected, open the format inspector, choose cell, and set the data format to match the format used originally in column B.
  • Delete the temporary column C.

It looks like a lengthy procedure, but should take less time to do than it did to describe.

Regards,

Barry

Hi Gillylal Fordham-Lear,

Here is a step-by-step method using several extra columns:

Formula in C2 YEAR(B2)

Formula in D2 MONTH(B2)

Formula in E2 DAY(B2)

Formula in F2 DATE(C2+1,D2,E2)

Here is a method that combines all the formulas into a single formula:

Formula in C2 DATE(YEAR(B2)+1,MONTH(B2),DAY(B2))

Don't worry about how Numbers displays the Date. You can format New Date to suit:

Select the New Date column and Copy.

Menu > Edit > Paste Formula Results to "fix" the dates.

Please reply with questions.

Regards,

Ian.

"Hope you now realise that this was an honest mistake by someone who's not as experienced as you, and not a willful attempt to waste anyone's time."

I was pretty sure an attempt to waste time was not the case.

I see that Ian and I have both responded to the same copy of the question. I did send a report in on the other one requesting it be deleted as it was a duplicate. I hadn't noticed at that time that they had different names.

Just went back to check—The other one has the earmarks of a 'first post (level 1 and 4 points)', so I must have chose(n) wisely (or at least luckily).

Deleting the a post is something that can only be done by the hosts (moderators). The person posting can edit the post within a period of about 15 minutes after posting, but can't remove it.

I wouldn't worry about having a second name registered. Shouldn't be an issue if you stick with this one and just avoid using the other. I did do a short search, which turned up several sites with instructions on cancelling an Apple ID, but none from "official" sources.

Regards,

Barry