Dates in Excel - date entry with last two digits of year xx sometimes gives 19xx instead of 20xx

Question

When I enter dates into Excel, I usually just type them as mm/dd/yy where mm is the digit(s) of the month, dd the digit(s) of the date, and yy the last two digits of the year - which for my purpose is always years in the current (21st) century, so full year is 20yy.

Usually, the date is auto-completed and appears in the Excel table as mm/dd/20yy, or however I have formatted that to display.

However, sometimes the date is auto-completed and appears as mm/dd/19yy.

I don't have a sense of when the auto-complete is correct and when it isn't relative to the information I input - for example, I just input a date as 3/18/22 and it came out correctly as 3/18/2022 and yet when I input 9/24/38 a second later it came out as 9/24/1938.

Is there a way to make sure that the auto-complete for the date completes it as 20yy every time?

Answer 1

This is a Windows setting that is used by Excel.

Click the Start button or press the Windows key.

Type control panel and press Enter.

Click Clock and Region.

Click Change date, time or number formats.

Click Additional settings...

Activate the Date tab.

You can specify here how Windows (and hence Excel) treats two-digit years.

Answer 2

When you type a date that uses a two-digit year, Excel uses the following centuries: | Two-digit year typed | Century used | | :--- | :--- | | 00-29 | 21st (year 2000) | | 30-99 | 20th (year 1900) |

It can be changed through the Windows control panel: for me, it was Settings / Time and Language / Region / Additional settings ' Date tab, "When a two digit year is entered, interpret a year between" box, type the cutoff year that you want, and then click OK.

Answer 3

Thanks to both HansV and Bernie Deitrick for their answers!

My screen looked like HansV's (I'm running Excel 2019 under Windows 11), and yet strangely the default (last two digits under 29 gives 20xx, last two digits 30 and over gives 19xx) actually worked like Bernie's.

Still, setting the end date for conversion at 2099 caused the start date for conversion to set to 2000, so all dates will now default to the 21st century.