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

Setting an Alarm

By using the alarm capabilities of Windows, you can make sure you never miss another important time again. This tip shows ...

Discover More

Spacing Before and After Lists

When formatting a document that uses lists, you may want to adjust the space that appears just before and just after the ...

Discover More

Keeping Documents at a Single-Page View

Word allows you to display either a single page at a time or, with larger monitors, multiple pages. If Word displays your ...

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 occurs within a given month? Here's how you can find the desired ...

Discover More

Rounding to the Nearest Quarter Hour

When entering times in a worksheet, you may have a need to round whatever you enter to the nearest 15-minute increment. ...

Discover More

The Last Business Day

Many businesses need to know when the last business day of the month occurs. This tip discusses several ways you can ...

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 two more than 7?

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.