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.
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!
Want to push a date to some pre-defined day of the month? Here are some ways to force the issue.Discover More
Excel is great when it comes to working with dates and times. You can even do math on dates. One such easy manipulation ...Discover More
Need to know the number of months between two dates? It's easy to figure out if you use the DATEDIF function.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.