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
Perhaps the simplest way will be using background error checking as here https://support.office.com/en-us/article/Convert-dates-stored-as-text-to-dates-8df7663e-98e6-4295-96e4-32a67ec0a680
That's important in your Import Text wizard you select to import as text, not as general. With this option query itself won't make any conversions.
After that copy/paste first cell in your column to any another empty column. You shall see error mark on top left level of the cell where data pasted. Next select and copy/paste the rest of your data under this cell - now all shall be marked by error. Select all cells with error, click on error button menu and choose proper conversion
Here is the result (i'm on ISO date format)
If you have dates both before and after millennium you shall process them separately. Column into which you paste shall be in General (by default) or Date format.
- Good method. It's just a bummer not being able to do everything in one pass.
