Assuming Dates are in the Current Century

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


1

When Fred types 6/1/29 into a cell, Excel, as expected, assumes he means June 1, 2029. However, when he types in 6/1/30, Excel assumes Fred means June 1, 1930. Fred wonders if there is a way to get Excel to assume he wants 6/1/30 to mean 6/1/2030 rather than 6/1/1930.

This is actually a Windows setting, not an Excel setting. If you want to change it, then get out of Excel entirely and follow these steps if you are using Windows 11. (If you are using a different version of Windows, you may have to sleuth around just a bit as Microsoft has a really bad habit of making seemingly minor changes from one version of Windows to another.)

  1. In the Search box at the bottom of the screen, enter "control panel" (without the quote marks) and press Enter. Windows should start the Control Panel.
  2. Under the heading "Clock and Region," click the link Change Date, Time, or Number Formats. Windows displays the Region dialog box.
  3. Click the Additional Settings button, at the bottom-right of the dialog box. Windows displays the Customize Format dialog box.
  4. Make sure the Date tab is displayed in the dialog box.

In the Calendar area of the dialog box you can see a century range. By default, this is set to 1950 and 2049 and represents the centuries that will be applied to two-digit years. Type in 51, and Windows assumes you are wanting 1951; type in 22 and Windows assumes you are wanting 2022.

Astute readers will notice an oddity here—Excel actually uses a century of 1930 to 2029, not 1950 to 2049. The trick, though, is that if you change the Windows default to something else, then Excel will start to pay attention to what you specify. Thus, you could change the upper limit in Windows to 2099, which gives you the century of 2000 to 2099, and Excel will take notice. If you do this, then anything you enter, from 00 to 99, will be assumed to be a year within the current century.

A good article on one of the Microsoft websites relative to this issue can be found here:

https://learn.microsoft.com/en-us/troubleshoot/microsoft-365-apps/excel/two-digit-year-numbers

Making the change in Regional Settings is a good way to deal with the situation on your machine. Understand, though, that since the change is being made in Windows, it will affect any program running on your system. If you don't want to affect other programs, or if you routinely use more than one computer, it may be best to slightly modify how you enter dates—always include four digits for the year. In this way, you don't leave Excel guessing and will aways get the century you want for your dates. In other words, type 6/1/2030 and you remove all ambiguity about which year you want.

This four-digit-year advice is particularly important when it comes to using some of the date-related functions in Excel. For instance, you can use a two-digit year with DATEVALUE, and Excel will assume a century based on a 1930 to 2029 century, regardless of any changes you make in Regional Settings.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12839) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, 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

Creating a CSV File

Need to get your data into a format that can be easily read by other programs? Chances are good that a simple CSV file ...

Discover More

Opening a Workbook but Disabling Macros

Macros that run automatically when you open or close a workbook are quite helpful. You may not want them to run, however, ...

Discover More

Converting Text Inside Double Asterisks to Bold

In plain-text documents, it is not uncommon to see asterisks used around text to indicate what should be considered bold ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Advancing Dates to a New Year

If you store dates in your worksheets, you may want to update those dates at the end of the year. This tip explains ...

Discover More

Calculating Dates for Thanksgiving

Ever wonder how to calculate the date for Thanksgiving in the United States? In this tip you discover not only that, but ...

Discover More

Automatically Advancing by a Month

Excel allows you to perform quite a few operations using dates in your worksheet. Sometimes, however, the answer may not ...

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 three more than 1?

2025-07-05 16:00:18

J. Woolley

The Tip notes that Excel's DATEVALUE function ignores any change to the Windows Regional Calendar; it still thinks the 21st century ends after 2029, so DATEVALUE("12/25/30") returns 12/25/1930. DATEDIF, EDATE, EOMONTH, YEAR, and YEARFRAC have similar problems when a text date has a 2-digit year.
On the other hand, VBA's DateValue function recognizes changes to the century cutoff year appropriately; if you change the century cutoff to 2049, for example, VBA.DateValue("12/25/30") returns 12/25/2030.
My Excel Toolbox includes DATEVALUE2 to address this issue; here's an abbreviated version:

Function DATEVALUE2(Date_Text As String) As Date
DATEVALUE2 = VBA.DateValue(Date_Text)
End Function

Date_Text can be any recognizable text date; for example, "25-Dec-30" is still Christmas Day on my computer, not the day before New Years Eve.
See https://sites.google.com/view/MyExcelToolbox/


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.