Forum Discussion
2 Digit year when importing
Change the way two-digit years are interpreted
IMPORTANT: To ensure that year values are interpreted as you intended, type year values as four digits (for example, 2001, not 01). By entering four-digit years, Excel won't interpret the century for you.
If you enter a date with a two-digit year in a text formatted cell or as a text argument in a function, such as =YEAR("1/1/31"), Excel interprets the year as follows:
00 through 29 is interpreted as the years 2000 through 2029. For example, if you type the date 5/28/19, Excel assumes the date is May 28, 2019.
30 through 99 is interpreted as the years 1930 through 1999. For example, if you type the date 5/28/98, Excel assumes the date is May 28, 1998.
In Microsoft Windows, you can change the way two-digit years are interpreted for all Microsoft Windows programs that you have installed.
Click the Start button, and then click Control Panel.
Do one of the following:
In Windows Vista, click Clock, Language, and Region.
In Windows XP, click Date, Time, Language, and Regional Options.
Click Regional and Language Options.
Do one of the following:
In Windows Vista, in the Regional and Language Options dialog box, click the Formats tab.
In Windows XP, in the Regional and Language Options dialog box, click the Regional Options tab.
Do one of the following:
In Windows Vista, click Customize this format.
In Windows XP, click Customize.
Click the Date tab.
In the When a two-digit year is entered, interpret it as a year between box, change the upper limit for the century.
As you change the upper-limit year, the lower-limit year automatically changes.
Source: Microsoft Office Support Website
https://support.office.com/en-us/article/Change-the-date-system-format-or-two-digit-year-interpretation-aaa2159b-4ae8-4651-8bce-d4707bc9fb9f
Thanks for the suggestion. It looks like it should work but I have not been successful using it. I did a simple test setting the date range 1976-2075 and put "12/31/50" in a notepad txt file. I restarted, then attempted to import using different format types. Date and General came in as 12/31/1950.
It's not necessary to restart the PC. With your changed regional settings you may do small exercise:
1) Enter in any cell 12/31/50 - Excel displays 12/31/2050
2) Get External Data from your txt file; or copy/paste dates from it into the Excel sheet; or enter into any cell =DATEVALUE("12/31/50") - for all above the result will be 12/31/1950
I don't know exactly how the text parser works but it looks like it uses default settings for 2-digits year interpretation and ignores calendar settings in Regional settings block.
Perhaps there are some settings in the registry, i don't know. But in any case all such decisions could have side effect on another applications and/or another Excel files. Especially if you share your files with someone else (not necessary files in subject).
As for me personally i'd prefer to do all transformations within the file i work with, not changing the environment. That could be more time consuming, but usually is more reliable. For this particular case i'd use helper columns like
=IF([parsed cell] <= 36525, [parsed cell]+36525, [parsed cell])
which returns all days within 21st century, and after that work with it.
Anyway, Excel always suggests few ways to achieve the same result, which one to choose depends on your goals and personal preferences.
-