Advancing Dates to a New Year

Frank has many cells in his worksheet that contain 2022 dates in the format mm/dd/yy. He wonders how he can change all of these to 2023 dates. Find and replace won't let him search for "/22" and replace it with "/23."

The very first question to answer in this situation is whether the cells contain actual date values or if they contain text values. You can tell if this is the case by looking at the value displayed in the cell and comparing it to what is shown in the Formula bar. If they are exactly the same, then you can assume that the cells contain text values.

Chances are good, however, that Frank's cells contain date values. Why? Because if he searched for "/22" and replaced with "/23," then the dates would have been updated had they been text values. In his case, there is a good chance that even though the cells showed a date such as "12/3/22," the Formula bar would have shown the date similar to "12/3/2022."

In that case, all Frank needs to do is to search for "/2022" and replace it with "/2023" and Excel will update the dates properly to the desired new year. I should say that the latest versions of Excel will do this; older versions may not be smart enough to figure out that you want to change a date. In that case, if your dates are all in a single column, you can add a helper column to the right of those dates and use a simple formula to calculate the new dates:

=A1+365

Since dates are stored internally by Excel as numbers, this formula adds 365 days to the original date in cell A1. If you are dealing with a leap year, you'll need to add 366 days instead, or you can use a slightly more complex formula:

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

There is another easy way you could use the helper column. To the right of your first date, enter the updated date. In other words, if the first date is in cell A1 and it is something like 1/3/22, to the right of that, in cell B1, you would enter 1/3/23. Now, let's assume that your original dates are in cells A1:A150. Select cells B1 (which contains 1/3/23) through B150. Now press Ctrl+E, and all the empty cell you selected in column B should be updated with new dates.

Regardless of how you decided to use the helper column, you can now select all the modified dates in the helper column, copy them, use Paste Values to put them into the original column, and then delete the helper column.

If you don't want to use a helper column, you can use Paste Special to make the modification. Follow these steps:

1. In a blank cell, enter the value 365.
2. Select that cell and press Ctrl+C to copy the value to the Clipboard.
3. Select the cells containing the dates you want to modify.
4. Display the Home tab of the ribbon.
5. Click the down arrow next to the Paste tool and, from the resulting choices, choose Paste Special. (It should be the final choice in the options.) Excel displays the Paste Special dialog box. (See Figure 1.)



Figure 1. The Paste Special dialog box.

6. Click the Add option, available in the Operation group.
7. Click OK.

Excel, at this point, adds the value in the Clipboard (365) to all the cells you selected in step 3. You can now delete the cell you created in step 1, and you may need to reapply formatting to the cells you selected in step 3.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (734) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.