Changing How Excel Determines which Year to Use

Written by Allen Wyatt (last updated February 7, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


11

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.

Note:

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

MORE FROM ALLEN

Sequentially Inputting Information

When entering data in a worksheet, you may only want to add information to the cells in a particular range. You can ...

Discover More

Stepping through Sentences

Need to select an entire sentence at once? You can do so by creating a short macro that does the task for you, or you can ...

Discover More

Backwards Date Parsing

Enter information into a worksheet, and you come to anticipate (and count on) how Excel will interpret that information ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Copying Dates a Year Into the Future

Need to copy a range of dates and update them to a different year? Here are a number of ways to accomplish this task with ...

Discover More

Determining If a Date is between Other Dates

Need to figure out if one date is between two other dates? There are a wide variety of formulaic approaches you could use ...

Discover More

Converting UNIX Date/Time Stamps

If you import information generated on a UNIX system, you may need to figure out how to change the date/time stamps to ...

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 seven minus 7?

2024-02-08 19:51:54

Tomek

@nickie Sr.:
To expand on my previous comment from today:
If the fraction you enter can be interpreted as a date, Excel will usually convert it to date, otherwise it will treat it as text, but not as number.
You could, however, format the cells as fractions with as many digits in the denominator as you wish, and then fractions you enter in them will not be treated as dates but as numbers. Your post is quite old, so I am not sure that you will read my comment, but if you do, please let me know.


2024-02-08 19:43:28

Tomek

@Rene:
True, usually, 9/12 is treated as date, and if you enter =9/12 - as a formula. The exception is if the cell is formatted as a fraction, by using one of the predefined fraction formats or a custom format e.g.,
# ??/??
In such case 9/12 will be treated as a fraction, and displayed as such. With the custom format as above, it would be displayed as 3/4 instead of 9/12 though. You can force the denominator to be whatever you wish, so in this case you could use format # ??/12 and get 9/12 displayed. Beware though, that the fraction will be rounded to the nearest one fitting the format, so 7/13 with format # ??/12 will be displayed as 6/12. With the format allowing one digit denominator (# ?/?) it would be rounded to 1/2.


2024-02-07 04:32:33

Rene

@ Willy Vanhaelen
9/12 is only a fraction if '=' is added first.


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

Jim

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
Beep
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 "", ""
Else
ThisWorkbook.Activate
xSh.Activate
xTarget.Select
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")
Else
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
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.