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
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:
Figure 1. The Paste Special dialog box.
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.
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!
Dates can be entered into a worksheet in any number of unique or novel ways. Working with those dates can be a challenge, ...
Discover MoreNeed to know the number of months between two dates? It's easy to figure out if you use the DATEDIF function.
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 MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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?
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