Changing How Excel Determines which Year to Use

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.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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 Microsoft 365.

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. ...


Converting Word to German

Want to convert your usage of Word from one language to another? It's not as easy as one would hope, as you'll discover ...

Discover More

Using SUM In a Macro

Want to use a worksheet function (such as SUM) from within a macro? Here's how easy it is to accomplish the task.

Discover More

Smart Quote after Em Dash Faces Wrong Direction

When using smart quotes in your typing, Word tries its best to figure out which way the quote you just typed should face. ...

Discover More

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!

More ExcelTips (ribbon)

Calculating an Age On a Given Date

Start putting dates in a worksheet (especially birthdates), and sooner or later you will need to calculate an age based ...

Discover More

Every Second Tuesday

Need a way to enter dates from every second Tuesday (or some other regular interval)? Excel makes it easy, providing ...

Discover More

Years in Which a Date Occurred on a Particular Day

If you need to know the years in which a particular date occurred on a specific day of the week, there are a number of ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


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 9 - 3?

2019-07-15 06:17:06

Willy Vanhaelen

@nickie Sr

9/12 is eiter a fraction or a date, both numeric. If you want your entry to be displad as 9/12 you must enter it as text by first formatting the cell as text or you can enter 9/12 preceded by an apostrphe in any cell: '9/12. The apostrophe will not be displayd but it tells Excel you want your entry to be text.

2019-07-13 11:53:42

nickie Sr.

I have yet to hear how I can stop getting a unwanted date in a cell. All I want to do is add a number such as 9/12. This not meant to be a date or formula.

2018-12-22 14:47:49

J. Woolley

@Jim: Read the first paragraph of the tip. Of course, you're right; it is a silly problem, but none the less interesting.

2018-12-22 14:02:21


I have a collection of cells (actually, a column) formatted as 'mm/dd/yyyy'; if I type 5/25 in a cell, the cell displays 05/25/2018 if the current year is 2018, as I would expect. However, if I type 5/25/19, the cell displays 05/25/2019. I don't see the problem.

2018-12-22 12:52:49

J. Woolley

By the way, my solution (posted in the previous comment) uses:
xTarget.Value = DateSerial((y + 1), Month(v), Day(v))
When the current year is 2020 (leap year) and the current date is 6/1/20, entering 2/29 results in 3/1/21 and Undo yields 3/1/20, so there is no way to enter 2/29/20. This could be fixed, but I will leave this as an exercise for the reader.

The tip uses:
Target.Value = DateAdd("yyyy", 1, Target)
When the current year is 2020 (leap year) and the current date is 6/1/20, entering 2/29 results in 2/28/21 and there is no Undo; however, DateAdd("yyyy", -1, "2/28/21") yields 2/28/20.

2018-12-22 11:58:10

J. Woolley

There are three problems with the event handler described in this tip:
1. It becomes impossible to enter any date for the current year that is earlier than the current date.
2. Any cell with a formula that results in a date for the current year that is earlier than the current date will be changed to a constant date value advanced by one year. For example, if today is 6/1/18, then the formula =TODAY()-1 becomes the constant value equivalent to 5/31/19.
3. It only applies to a single worksheet. (Maybe this is a benefit, not a problem.)

Here is my solution, which addresses all three problems. (Apologies for poor VBA code format when posting comments here.)

1. Press Alt+F11 to open the Visual Basic Editor (VBE).
2. Under VBAProject > Microsoft Excel Objects, click ThisWorkbook and press F7 to open the Code pane.
3. Copy the following VBA code for ThisWorkbook (which applies to any worksheet in ThisWorkbook and ignores cells having a formula):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count = 1 And Not Target.HasFormula And IsDate(Target.Value) _
Then YearCheck_Do Sh, Target, 0
End Sub

4. From the VBE menu, pick Insert > Module.
5. Copy the following VBA code for this new Module (which must be in the same VBAProject):

Public Sub YearCheck_Do(ByVal Sh As Object, ByVal Target As Range, ByVal UndoRedo As Integer)
Static xSh As Object, xTarget As Range
If UndoRedo = 0 Then
v = Target.Value
y = Year(v)
If y = Year(Date) And v < Date Then
Set xSh = Sh
Set xTarget = Target
Application.EnableEvents = False
xTarget.Value = DateSerial((y + 1), Month(v), Day(v))
Application.EnableEvents = True
s = "Undo '" + CStr(y + 1) + "' in " + Replace(xTarget.Address, "$", "")
Application.OnUndo s, (ThisWorkbook.Name + "!YearCheck_Undo")
End If
Application.OnRepeat "", ""
v = xTarget.Value
y = Year(v)
Application.EnableEvents = False
xTarget.Value = DateSerial((y + UndoRedo), Month(v), Day(v))
Application.EnableEvents = True
s = "' in " + Replace(xTarget.Address, "$", "")
If UndoRedo < 0 Then
s = "Redo '" + CStr(y) + s
Application.OnRepeat s, (ThisWorkbook.Name + "!YearCheck_Redo")
s = "Undo '" + CStr(y + 1) + s
Application.OnUndo s, (ThisWorkbook.Name + "!YearCheck_Undo")
Application.OnRepeat "", ""
End If
End If
End Sub

Private Sub YearCheck_Undo()
YearCheck_Do Nothing, Nothing, -1
End Sub

Private Sub YearCheck_Redo()
YearCheck_Do Nothing, Nothing, 1
End Sub

Now if today is 6/1/18 and you type 7/1, Excel will assume the year 2018. But if today is 6/1/18 and you type 5/1 or 5/1/18 or 5/1/2018, the year will be changed from 2018 to 2019. If you wanted the year to remain 2018 instead of changing to 2019, then click Undo or press Ctrl+Z. The only way to enter a date for the current year that is earlier than the current date becomes a two-step process: First enter the desired date, then click Undo or press Ctrl+Z. After using Undo, it is possible to click Repeat of press Ctrl+Y (or F4) to Redo the change to 2019. This Undo/Redo only applies to the last date entered for the current year that was earlier than the current date. (Note: Excel does not permit VBA control of OnRedo, but OnRepeat has the same keyboard shortcuts.)

2018-12-22 05:15:33

Michael Avidan

Sorry... Waht I meant was:
Shouldn't it read: If Target > Date Then...

2018-12-22 05:14:26

Michael Avidan

I wonder....
Shouldn't it read: If Target < Date Then...

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

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.