by Allen Wyatt
(last updated September 17, 2018)
Juan has a column (column A) of dates in the year 2014. 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 2015.
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(2015,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:
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, 2012, then the result of the DATE formula would be March 1, 2015. (It is 2015 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, 2012, becomes February 28, 2013. (It is 2013 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:
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, 2012, it is changed to February 29, 2013. Since that is an invalid date, it is parsed by Excel as a text value, meaning it is no longer a date.
There is another approach that was suggested be several ExcelTips subcribers, as follows:
Figure 2. The Paste Special dialog box.
At first blush, 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, and 2013.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
When working with dates, you may need to figure out all the dates on which weeks end in a given year. There are several ...Discover More
The NETWORKDAYS worksheet function can be used to easily determine the number of work days (Monday through Friday) within ...Discover More
Doing math with dates is quite easy in Excel. As this tip illustrates, this fact makes it easy to figure out the Nth ...Discover More
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.