Written by Allen Wyatt (last updated February 19, 2024)**This tip applies to** Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365

Juan has a column (column A) of dates in the year 2018. He wants to copy that range of dates into another worksheet in the same workbook. He needs to make the dates all the same, except for the year, which should be 2019.

There are quite a few different ways you can go about accomplishing this task. One way is to use a formula to create the new dates. Assuming the original dates are in column A (as Juan noted) and that the worksheet is named Original Dates, you could use the following formula in cell A1 of a different worksheet:

=DATE(2019,MONTH('Original Dates'!A1),DAY('Original Dates'!A1))

You can then copy the formula down as many cells as desired. If you prefer, you can use an even shorter formula:

=EDATE('Original Dates'!A1,12)

Remember that when you use a formulaic approach, Excel may not automatically format the result to look like a date. That's easy enough to fix; just apply the cell formatting you want.

There is an important difference between the two formulas that deal with how they resolve what happens if the original date is leap day (February 29). In the case of the DATE function, Excel treats the new date as March 1. Thus, if the original date was February 29, 2016, then the result of the DATE formula would be March 1, 2017. (It is 2017 because the formula is "hardwired" to use that year.)

In the case of the EDATE function, February 29 is rendered as February 28. Thus, February 29, 2016, becomes February 28, 2017. (It is 2017 because the EDATE function is told to use a date 12 months later than the original.)

Whichever formula approach you use (and there are several others), once they are in place you can select all the formulas, press **Ctrl+C** to copy them to the Clipboard, display the Home tab of the ribbon, click the down-arrow under the Paste tool, and select to Paste Values. This does away with the formulas and leaves you with actual dates in the cells.

Speaking of copying and pasting, that brings up another way to get the dates to the new worksheet:

- Select the original range of dates.
- Press
**Ctrl+C**to copy them all to the Clipboard. - Switch to the new worksheet and select the first cell (A1) where you want the dates pasted.
- Press
**Ctrl+V**to paste the dates. They should be exactly the same as on the original worksheet and the range you just pasted should be selected. - Press
**Ctrl+H**. Excel displays the Replace tab of the Find and Replace dialog box. (See Figure 1.) - In the Find What box put the year you want to change (2018).
- In the Replace With box put the year to which you want to change (2019).
- Click Replace All. Excel lets you know how many replacements it made.
- Close the Find and Replace dialog box.

** Figure 1.** The Replace tab of the Find and Replace dialog box.

Excel is smart enough to know that you want to replace years, and in so doing it "reevaluates" the resulting dates. For most dates this is not a problem, but it is for leap days. If the original date is February 29, 2016, it is changed to February 29, 2017. Since that is an invalid date, it is parsed by Excel.

There is another approach that was suggested by several ExcelTips subscribers, as follows:

- Select the original range of dates.
- Press
**Ctrl+C**to copy them all to the Clipboard. - Switch to the new worksheet and select the first cell (A1) where you want the dates pasted.
- Press
**Ctrl+V**to paste the dates. They should be exactly the same as on the original worksheet. - In an unused cell, enter the value 365.
- Select the cell used in step 5.
- Press
**Ctrl+C**to copy the value to the Clipboard. - Select the range of cells you pasted in step 4.
- Display the Home tab of the ribbon.
- Click the down-arrow under the Paste tool and then choose Paste Special. Excel displays the Paste Special dialog box. (See Figure 2.)
- Select the Add radio button.
- Click OK.
- Format the selected cells to use whatever date format you desire.

** Figure 2.** The Paste Special dialog box.

At first glance, it appears that all your dates are updated to be one year later than they were originally. In many instances this will be correct but, again, leap days will mess you up. If the original dates are in a year that contains a leap day, adding 365 to them doesn't mean they will be a year later because the leap year contains 366 days. Thus, it is best to use one of the other methods described in this tip.

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

**Save Time and Supercharge Excel!** Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out *Excel 2010 VBA and Macros* today!

Those in Europe use a date format that is different than those in the US; this is not news. But what if you need to ...

Discover MoreNeed to figure out if a given year is a leap year? It's not as easy as you think! This tip provides a plethora of ways ...

Discover MoreDoing math with dates is easy in Excel. Doing math with old dates, such as those you routinely encounter in genealogy, is ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2024-02-23 12:17:13

Tomek

It seems that posting pictures was fixed so I will try again posting a screenshot for my comment of 2024-02-23 09:56:49

(see Figure 1 below)

**Figure 1.**

2024-02-23 09:56:49

Tomek

Rather than telling Juan how to copy and modify his data (what he asked for) I would suggest creating a workbook that generates the required column of dates (what he actually may want). This solution is based on the first tip approach (DATE formula) but rather than hard coding the year, the year can be input into a selected cell.

So here are the steps:

· Place your original dates in column A staring with cell A2.

· In cell B1 enter the target year.

· In cell B2 enter formula "=DATE(B$1,MONTH($A2),DAY($A2))"

· Copy that formula down for as many rows as you need.

A this point you should have new dates in column B, with any original Feb. 29 in leap year converted to Mar. 1 of a non-leap year. Also, Feb. 28 will stay always unchanged. If this is not what you want, you need to modify the formulas that involve Feb 28 or Feb. 29 in the original dates. You need to do this only once, afterwards you can just use the spreadsheet by entering a new target year.

You have four choices - two for Feb 28 and two for Feb 29 as original date:

· If you want Feb. 28 to always stay Feb 28 do nothing.

· If you want Feb. 28 to be treated as end of month envelop the formula in EOMONTH function: "=EOMONTH(DATE(B$1,MONTH($A4),DAY($A4)),0)"

· If you want Feb. 29 to become March 1, do nothing, the DATE function does it automatically.

· If you want Feb. 29 to be treated as end of month use EOMONTH function but subtract 1 from the day:

"= EOMONTH(DATE(B$1,MONTH($A9),DAY($A9))-1,0)"

[{fig}] a screenshot of a demo spreadsheet using this approach for combinations of normal and leap year source and target.

You can use such approach directly in your spreadsheet or if you just want explicit dates in a column you can copy the generated dates and paste them into your other spreadsheet using Paste-Special-Values followed by Paste-Special-Formats.

2024-02-16 17:04:55

Ronmi

2019-12-11 12:59:48

kim kauer

It is almost perfect, I just need the DAY too for the year 2020 and beyond -

Trying to create a daily plumbing schedule.

Got a version of Excel that uses the
ribbon interface (Excel 2007 or later)?
**This site is for you!** If you
use an earlier version of Excel, visit
our *ExcelTips* site focusing on the menu interface.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2024 Sharon Parq Associates, Inc.

## Comments