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


2

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

Pasting a Hyperlink

When you paste information into a document, you can specify that it be inserted as a hyperlink rather than as normal ...

Discover More

Boxes in Boxes

When you insert a text box within another text box, you may expect any text in the outer text box to wrap around the ...

Discover More

Moving Custom Formats to Number Formatting Categories

Moving your custom formats into a formatting category other than "custom" isn't something you can do in Excel. Here's ...

Discover More

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!

More ExcelTips (ribbon)

Beginning of a Future Week

Because Excel stores dates internally as serial numbers, it makes doing math with those dates rather easy. Even so, it ...

Discover More

Starting Date for a Week Number

If you need to determine the starting date for a particular week in a year, your first task is to identify which week ...

Discover More

The Last Business Day

Many businesses need to know when the last business day of the month occurs. This tip discusses several ways you can ...

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 8 + 2?

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/


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.