Copying Dates a Year Into the Future

by Allen Wyatt
(last updated November 29, 2014)

12

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:

=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, 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:

  1. Select the original range of dates.
  2. Press Ctrl+C to copy them all to the Clipboard.
  3. Switch to the new worksheet and select the first cell (A1) where you want the dates pasted.
  4. 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.
  5. Press Ctrl+H. Excel displays the Replace tab of the Find and Replace dialog box. (See Figure 1.)
  6. Figure 1. The Replace tab of the Find and Replace dialog box.

  7. In the Find What box put the year you want to change (2014).
  8. In the Replace With box put the year to which you want to change (2015).
  9. Click Replace All. Excel lets you know how many replacements it made.
  10. Close 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:

  1. Select the original range of dates.
  2. Press Ctrl+C to copy them all to the Clipboard.
  3. Switch to the new worksheet and select the first cell (A1) where you want the dates pasted.
  4. Press Ctrl+V to paste the dates. They should be exactly the same as on the original worksheet.
  5. In an unused cell, enter the value 365.
  6. Select the cell used in step 5.
  7. Press Ctrl+C to copy the value to the Clipboard.
  8. Select the range of cells you pasted in step 4.
  9. Display the Home tab of the ribbon.
  10. Click the down-arrow under the Paste tool and then choose Paste Special. Excel displays the Paste Special dialog box. (See Figure 2.)
  11. Figure 2. The Paste Special dialog box.

  12. Select the Add radio button.
  13. Click OK.
  14. Format the selected cells to use whatever date format you desire.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Selecting Tabs in Dialog Boxes

Dialog boxes normally present information in a series of tabs. If you want to move from tab to tab without taking your hands ...

Discover More

Word 2007 Documents Unreadable in Earlier Word Versions

Some people report that even if they save a Word 2007 document in a format suitable for earlier versions of Word, the ...

Discover More

Specifying a Language for the TEXT Function

You may want to use Excel to display dates using a different language than your normal one. There are a couple of ways you ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Weekdays in a Month

Want to find out how many of a particular weekday occur within a given month? Here's how you can find the desired ...

Discover More

Unique Military Date Format

Some industries (such as the military) have special formatting that they use to represent dates. Here is one such format and ...

Discover More

Deciphering a Coded Date

It is no secret that Excel allows you to work with dates in your worksheets. Getting your information into a format that is ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three more than 5?

2014-12-09 22:51:53

Peter Moran

Hi,

Re Dave Weeks query on moving working days a year ahead:
Just add 364 to the previous date - which give the same day of the week the next year.

Obviously for leap years it is a different no of days before and after Feb 29th.

Peter Moran
Melbourne, Aust.


2014-12-01 15:17:48

Michael (Micky) Avidan

@Tom Kvalø,
1) What do you' exactly' mean by: "EDATE gives you two(!) 28 Feb ???
2) Would you be so kind to upload an Excel workbook - with some samples - to some free file hosting site ?
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


2014-12-01 10:23:54

Tom Kvalø

In the year after a Leap year, EDATE gives you two 28 feb.....
Use

=if(mod(year(a1),4)=0,a1+366,a1+365)

In the above, some have written 356 in stead of 365, which gives both wrong week and date. This works for dates between 1/1-1901 to 31/12-2099.


2014-11-30 22:37:33

Shreepad S M Gandhi

There’s a formula to accomplish this task. Suppose Column A of Sheet1 has your dates for this year i.e. 2014.
Let’s say for example A1 has a date 24-Nov-14.
Go to cell A1 of Sheet2 and type the formula ‘=DATE(YEAR(Sheet1!A1)+1,MONTH(Sheet1!A1),DAY(Sheet1!A1))’
You may drag down A1 of Sheet2 as may be desired.
This will return the same date but for the year 2015. Hope this serves the purpose.


Allen, this was mailed at etanswers@tips.net.

I know not why it isn't credited.


2014-11-30 08:30:27

Michael (Micky) Avidan

@Annroehm + Peter Atherton,
What is the purpose of: 356 ???
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL



2014-11-30 08:28:16

Michael (Micky) Avidan

@Dave Weeks,
Is this what you are looking for ?
http://postimg.org/image/x2oo0ztut
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


2014-11-29 20:57:34

Peter Atherton

Oops, ignore my last post. Try this UDF:

Function NEXTYRWORKDAY(ByVal REF) As Date
Dim dte As Date
'Get Date for next year
If Year(REF) Mod 4 = 0 Or _
Year(REF) Mod 400 = 0 Then
dte = REF + 366
Else
dte = REF + 365
End If
'Modify to get workdays if nec
If WorksheetFunction.Weekday(dte) = 1 Then
dte = dte + 1
ElseIf WorksheetFunction.Weekday(dte) = 7 Then
dte = dte + 2
End If
NEXTYRWORKDAY = dte
End Function

Test Data shown

Dates TestDates
Wed 01/01/2014 Wed 01/01/2014
Thu 01/01/2015 Thu 01/01/2015
Fri 01/01/2016 Fri 01/01/2016
Sun 01/01/2017 Mon 02/01/2017
Mon 01/01/2018 Mon 01/01/2018
Tue 01/01/2019 Tue 01/01/2019
Wed 01/01/2020 Wed 01/01/2020
Fri 01/01/2021 Fri 01/01/2021
Sat 01/01/2022 Mon 03/01/2022
Sun 01/01/2023 Mon 02/01/2023
Mon 01/01/2024 Mon 01/01/2024
Wed 01/01/2025 Wed 01/01/2025
Thu 01/01/2026 Thu 01/01/2026
Fri 01/01/2027 Fri 01/01/2027
Sat 01/01/2028 Mon 03/01/2028


2014-11-29 20:36:59

Walter

Dave Weeks, Does =networkdays provide what you need?


2014-11-29 18:20:18

Peter Atherton

=if(mod(year(a1),4)=0,a1+366,a1+356) unfortunately gives the wrong week number.

Try:
=IF(MOD(YEAR(A10)-1,4)=0,A10+366,A10+356)+8



2014-11-29 13:13:32

Annroehm

Try this: =if(mod(year(a1),4)=0,a1+366,a1+356)


2014-11-29 12:26:08

Dave Weeks

Is there a way to take a range of workdays (Monday-Friday) and to advance them to the next year, again displaying only Monday through Friday?


2014-11-29 05:07:49

Michael (Micky) Avidan

So, to make a long story short - EDATE is the ultimate(!) function for the above task.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


This Site

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.

Newest Tips
Subscribe

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

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.