Advancing Dates to a New Year

Written by Allen Wyatt (last updated December 31, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


7

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.)
  6. Figure 1. The Paste Special dialog box.

  7. Click the Add option, available in the Operation group.
  8. 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.

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

Defining a Shortcut for a Macro

You can make running macros very easy if you assign a shortcut key to the macro. This tip demonstrates how easy it is to ...

Discover More

Displaying Path Names in the Menu Bar

Want the full path name for a document visible on the screen? Easily add it to a menu bar.

Discover More

Hiding Errors on Printouts

If there are error values in a worksheet, you may not want those error values to appear on a printout. Excel actually ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Calculating Differences in Months using Non-Standard Date Values

Dates can be entered into a worksheet in any number of unique or novel ways. Working with those dates can be a challenge, ...

Discover More

Calculating Months of Tenure

Need to know the number of months between two dates? It's easy to figure out if you use the DATEDIF function.

Discover More

Determining If a Year is a Leap Year

Need 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 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}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. 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 five more than 0?

2023-01-02 21:21:51

Peter

If you are working in a workbook with a financial year of July to June, say 2022-2023, it is important to replace all the 2023's before the 2022's.


2023-01-01 01:23:07

Tomek

If the dates are scattered throughout the sheet, as J. Woolley noted, then only Copy-PasteValues-Add option seems reasonable. For leap year conversion you can select Jan-Feb dates then run the conversion, and repeat the process separately for Mar.-Dec., copying 365 or 366 as indicated in Bill's comment and my reply to him.

Alternatively, a macro can be written that will do the conversion and account for leap/normal year conversion when run on a selection of cells. For me it seems to be an overkill though.


2023-01-01 00:58:14

Tomek

Bill,
You are right if you update from normal to a leap year. From leap year to normal you need to add 366 for Jan-Feb and 365 for the rest of the year.


2023-01-01 00:53:18

Tomek

All these approaches will work for Frank as both the old and the new year are not leap years.

The trick with Ctrl+E shortcut is especially neat as most people probably do not know this relatively new feature available only since Excel 2013. It is worth exploring as it can have many uses beyond what was shown in this tip. You can start with https://support.microsoft.com/en-us/office/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7

Now, about leap years: The formula =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) in a helper column is probably the best approach, as it will work for normal years, but also yield reasonable results if either the old or new year is a leap year; for the old leap year Feb. 29 will be converted to March 1 which is probably fine; for the leap new year there will be no Feb. 29 in the old year so no old date will be converted to Feb.29. If the dates are deadlines at the end of the month and neither Mar.1 in a non leap year nor Feb. 28 in a leap year is acceptable, then may be you can wrap the formula in the =EOMONTH(date, 0) function.

Interestingly, the Ctrl+E approach used on a range of dates of the leap year will for example convert 2020-02-29 into 2021-02-29, which obviously is not a valid date; as such this will no longer be a date but will get converted into text. The rest of the dates will be kept as Excel dates.


2022-12-31 16:53:03

JOHN BORG

Please note in this tip "Advancing Dates to a New Year" that the "FLASH FILL FEATURE" (press Ctrl+E) is not available in 2010 or earlier versions of Excel.


2022-12-31 10:35:24

J. Woolley

Frank never said all of the 2022 dates were in a single column. Perhaps they were in many scattered cells.


2022-12-31 09:33:10

Bill

For a leap year update, it seems you would need to add 365 to only January and February and then add 366 to March - December. Right?


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.