Changing How Excel Determines which Year to Use

by Allen Wyatt
(last updated December 22, 2018)

8

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

Setting the Default Print Preview Zoom Factor

Print Preview normally adjusts the zoom factor used for your document so that you can see an entire page at a time. If ...

Discover More

Understanding Click and Type

The Click and Type feature of Word allows you to use the mouse to click somewhere on a blank page, and then begin typing. ...

Discover More

Locking the Background Color

You can spend a lot of time getting the formatting in your worksheets just right. If you want to protect an element of ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Leap Years and Fiscal Periods

Need to figure out when a fiscal year ends when that period does not correspond to the calendar year? Here are some ways ...

Discover More

Parsing Non-Standard Date Formats

When you load data into Excel that was created in other programs, the formatting used for some types of data (such as ...

Discover More

Weekdays in a Month

Want to find out how many of a particular weekday occurs within a given month? Here's how you can find the desired ...

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}] 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 four minus 2?

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.