Modifying Default Year for Dates

by Allen Wyatt
(last updated January 9, 2016)

2

When entering dates into a worksheet, Excel provides quite a bit of latitude for how you enter them. One area where latitude is granted is when it comes to including a year. If you include a year (such as 3/2/08), that is great. If you don't include a year (such as 3/2), then Excel helpfully adds the current year to what you are entering.

Most of the time this isn't a problem. It can be a problem, however, for those entering dates that "wrap around" to last year. For instance, many people enter dates for the previous month or two in their worksheets. In most months this isn't a problem, because the past month or two is in the same year as the current month. It can be a problem during January and February, however, when you may be entering dates from November and December of the previous year.

One solution is to always enter the year when entering a date. It is unarguably faster to leave the year off during data entry and allow Excel to add it to your entry. Thus, it would be nice to come up with a way to enter dates during the first two months of the year and have the previous year's year appended to them.

One way to handle this is to change the system date on your computer. Within Windows, decrement the system date by one year. Then, all dates that you enter will change to last year. This has ramifications in other programs, however, unless you remember to change the system date back. It also can mess up your data entry if, during the latter part of January and early February, you start entering dates from this year, and Excel automatically appends last year's year to them.

Doing anything more complex necessarily involves the use of a macro. Consider the following example, which should be added to the code window for a worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target = "" Then Exit Sub
    If Target.Column <> 1 Then Exit Sub

    If DateValue(Target) > Date Then
        Application.EnableEvents = False
            Target = DateAdd("yyyy", -1, Target)
        Application.EnableEvents = True
    End If
End Sub

This macro automatically runs whenever there is a change in the worksheet. If the change being made is to a single cell in column A, and what is being entered is a date that is greater than today's date, then a year is subtracted from what is being entered.

This works great, provided you are routinely entering dates that are within either the last year or the months so far in the current year. If you specifically add a far-future date (such as entering 6/11/17 on 3/2/16), then the year is still incremented by one. The macro could, of course, be modified to check to see if the date being entered is in the last two months of a year, and that the date is actually being entered during the first two months of a year, before doing the decrement of the year.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3360) applies to Microsoft Excel 97, 2000, 2002, and 2003.

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

Storing AutoText Entries with a Document

AutoText entries can provide quite a bit of flexibility and power in a document. If you want to share those entries with ...

Discover More

Copying a File in VBA

Need to have your macro copy a file from one place to another? It's easy to do using the FileCopy command, described in this ...

Discover More

Copying Pictures with a Macro

Copying information using a macro is rather simple, although there are multiple ways you can do the copying. The most ...

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 (menu)

Recording a Data Entry Time

When entering information in a worksheet, it is common to also note a date or time corresponding to the entry. There are ...

Discover More

Jumping to the Start of the Next Data Entry Row

Want a quick way to jump to the end of your data entry area in a worksheet? The macro in this tip makes quick work of the ...

Discover More

Entered Values are Divided by 100

Enter a value into a cell and "poof!" it is automatically divided by 100. What's going on, and how can you fix it so that ...

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. 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 six less than 6?

2016-03-02 12:18:49

Chris

Donna,

Did you find an answer for your question? I am having the same issue.


2016-01-26 22:49:40

Donna

"If you include a year (such as 3/2/08), that is great. If you don't include a year (such as 3/2), then Excel helpfully adds the current year to what you are entering." This helpful feature is not working in my application. I am using Excel 2016 and Windows 7 Pro. When I input a date e.g. "jan 26" with the following date format dd/mm/yyyy, Excel returns 01/01/2026. I am stumped as to why it's doing this. It doesn't matter if I use English or Canada. The date is correct in Windows. Any suggestions as I would prefer to not have to enter the year every time. Thx.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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.