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
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.)
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.
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!
If you store dates in your worksheets, you may want to update those dates at the end of the year. This tip explains ...
Discover MoreEver wonder how to calculate the date for Thanksgiving in the United States? In this tip you discover not only that, but ...
Discover MoreExcel allows you to perform quite a few operations using dates in your worksheet. Sometimes, however, the answer may not ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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/
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments