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.
Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!
Because Excel stores dates internally as serial numbers, it makes doing math with those dates rather easy. Even so, it ...
Discover MoreIf you need to determine the starting date for a particular week in a year, your first task is to identify which week ...
Discover MoreMany businesses need to know when the last business day of the month occurs. This tip discusses several ways you can ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2025-07-07 10:46:25
J. Woolley
Re. my previous comment below, it has come to my attention that EDATE, EOMONTH, YEAR, and YEARFRAC will accept dates as serial numbers or text, but text is discouraged because of the 2-digit year problem. (DATEDIF is ignored here because it has other problems.) On the other hand, DATEVALUE and the previously described version of DATEVALUE2 will only accept dates as text. Therefore, I have modified DATEVALUE2 to accept dates as serial numbers as well as text; here's a new abbreviated version:
Function DATEVALUE2(Date_Value As Date) As Date
DATEVALUE2 = VBA.DateValue(Date_Value)
End Function
The result is a date serial number with truncated time. Notice VBA's data type Date recognizes a date/time serial number as well as text that can be interpreted as a date/time value; it is valid from January 1, 0100 (without time-of-day) to December 31, 9999. Excel's date serial number is only valid from January 1, 1900 (or 1904) to December 31, 9999. To work with earlier dates in Excel, use text values with 4-digit years (like "July 4, 1776"); in this case, Excel interprets DATEVALUE2's result as a negative serial number (i.e., days before Excel's earliest date).
My Excel Toolbox now includes EDATE2, EOMONTH2, YEAR2, and YEARFRAC2 functions to correct problems when a text date has a 2-digit year; they use methods similar to DATEVALUE2. For example, here's an abbreviated version of EDATE2:
Function EDATE2(Start_Date As Date, Months As Long) As Date
EDATE2 = WorksheetFunction.EDate(Start_Date, Months)
End Function
For an alternative to DATEDIF, consider My Excel Toolbox's TimeDif function:
=TimeDif(Start, Finish, [Approximate], [Conversational])
Start and Finish are numeric or text dates and/or times. TimeDif returns the difference between Start and Finish as text, which can optionally be exact, approximate, or conversational. The optional arguments are FALSE by default, yielding an exact result.
See https://sites.google.com/view/MyExcelToolbox/
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