by Allen Wyatt
(last updated December 22, 2018)
Brian would like to enter a date by typing in just the month and day. Excel assumes the year to be the current year, but he would like to have the year to be the one for the next time that date occurs. For instance, if today is 6/1/18 and Brian types 7/1, Excel automatically assumes a year of 2018. If Brian types 5/1 on the same day, then Excel should assume a year of 2019, as 5/1 is already in the past. Brian knows he can use a formula to have the date display in a different cell but wonders how he can get Excel to default to this approach as he is typing dates into cells.
Short of playing around with your system date or doing some editing to dates after entry, the solution requires the use of a macro. The following event handler will do the trick:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Target = "" Then Exit Sub If IsDate(Target) Then If Target < Date Then If Year(Target) = Year(Date) Then Application.EnableEvents = False Target.Value = DateAdd("yyyy", 1, Target) Application.EnableEvents = True End If End If End If End Sub
In order to add this macro to your workbook, simply right-click on the tab for the worksheet where you want it operative and then choose View Code. In the resulting code window, add the above macro.
Now, anytime you make a change in the worksheet, it is checked to see if it is a date. If it is, and the date is before today's date, then a year is added to the date. Note that the code checks to ensure that the year of the date being entered is the same as the current year. Without this check, any date in the past—even dates many years in the past—would be incremented. This is probably not intended, so the check is included.
The macro will not affect dates entered into multiple cells at once, nor will it affect dates entered as text values.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13601) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Enter information into a worksheet, and you come to anticipate (and count on) how Excel will interpret that information ...Discover More
Doing math with dates is easy in Excel. Doing math with old dates, such as those you routinely encounter in genealogy, is ...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
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.