Assuming Dates are in the Current Century

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.