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

Understanding Variables

Variables are used quite frequently in programming, and VBA programming is no exception. Each variable can have a data ...

Discover More

Changing the Color of a Tab's Leader Character

When you set tab stops for a paragraph, you can also specify leader characters to be used with the tab stop. If you want ...

Discover More

Tab Key Won't Move from Cell to Cell in Locked Worksheet

Normally the Tab key can be used to move from one cell to another in Excel. If this cell movement doesn't work for you, ...

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)

End-of-Month Calculations

Don't want to use the EOMONTH function to figure out the end of a given month? Here are some other ideas for discovering ...

Discover More

Listing Dates at Regular Intervals

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

Discover More

Unique Date Displays

Need to print an elapsed date in a strange format? It's easier to do than may appear at first glance. Here's a discussion ...

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 7 + 0?

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.