2 Digit year when importing

[msaintvincent]

I am importing using Text Import Wizard. Data is coming from older legacy system that has two digit years (mm/dd/yy). Sample data would be "12/31/49". Excel imports this as 12/31/1949 when it should be 12/31/2049.

Is there a way to control where the break point is that sets the four digit year as 19xx vs. 20xx?

[#2 · Accepted solution]

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.

1. Click the Start button, and then click Control Panel.

2. Do one of the following:

   • In Windows Vista, click Clock, Language, and Region.

   • In Windows XP, click Date, Time, Language, and Regional Options.

3. Click Regional and Language Options.

4. 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.

5. Do one of the following:

   • In Windows Vista, click Customize this format.

   • In Windows XP, click Customize.

6. Click the Date tab.

7. 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

Change the date system, format, or two-digit year interpretation

[#3]

I would just run some VBA after the text import. If you run the code below, it will ask you for the TWO DIGIT transition year between 1900/2000. For example, enter '50' not '1950'

Option Explicit
Option Base 0
Option Compare Text
Option Private Module

Sub FixTheDates()
''''''''''''''''''''''''''''''''''''''
' Allows changing dates with two-digit
' years, using the transition year of
' your choice. The user will be prompted
' for the transition year.
' Author: Chip Pearson, chip@cpearson.com
' License: Explicit Public Domain
''''''''''''''''''''''''''''''''''''''

    Dim Year2000 As Long
    Dim R As Range
    Dim RR As Range
    Dim Cutoff As Long
    Dim S  As String
    
    ' ensure Selection is not null
    If Selection Is Nothing Then
        MsgBox "The 'Selection' object is null (Nothing).", vbExclamation Or vbCritical, "Null Selection"
        Exit Sub
    End If
    
    ' ensure selection is an Excel.Range, not some other object (e.g. a Shape)
    If Not TypeOf Selection Is Excel.Range Then
        MsgBox "The 'Selection' object is a '" + TypeName(Selection) + "', not a range of cells." + vbNewLine + _
            "Please select the range of cells and run this procedure again.", vbOKOnly, "Invalid Selection"
        Exit Sub
    End If
 
    
    ' let the user choose the century transition.
    Cutoff = 30 '<<<<<<<<<<<<<<<< two digit values LESS than this are considered 1900 century.
                ' two digit values GREATER THAN OR EQUAL TO thias are considered 2000 century.
                ' E.g. Cutoff = 30 implies 26 means 2026 and 36 means 1936.
    
    S = "Enter the two-digit year at which the century cutoff occurs." + vbNewLine
    S = S + "Two digit years LESS than this number are consider to be in the 2000 century." + vbNewLine
    S = S + "Two digit years GREATER THAN OR EQUAL TO this number are considered to be in the 1900 century."
    
    ' prompt the user. the "50" is the default, but if the user clicks Cancel,
    ' the result is 0 and we get out.
    Year2000 = Application.InputBox(S, "Year Cutoff", "50", Type:=1)
    If Year2000 = 0 Then
        ' user clicked cancel
        Exit Sub
    End If
    
    ' ensure valid range
    If Year2000 <= 0 Or Year2000 >= 99 Then
        MsgBox "The value '" + Str(Year2000) + "' is not valid." + vbNewLine + _
            "It must be between 0 and 99 (exclusive).", vbExclamation Or vbOKOnly, "Invalid Data"
        Exit Sub
    End If
       
    ' set our range of cells
    Set RR = Selection
    If RR.Cells.Count = 1 Then
        ' if there is only one cell selected, the user likely forgot to
        ' select the proper range. ask her.
        If MsgBox("The selection contains only on cell." + vbNewLine + _
                "Did you forget to select the range?" + vbNewLine + vbTab + _
                "Click 'Yes' to continue with once cell or click 'No'" + vbNewLine + vbTab + _
                "to exit so you can select the proper range.", _
                vbYesNo Or vbQuestion Or vbDefaultButton2) = vbNo Then
            Exit Sub
        End If
    Else
        ' only one Area allowed
        If RR.Areas.Count > 1 Then
            MsgBox "You may only run this procedure on a single area (a rectangular range).", vbOKOnly, "Invalid Selection"
            Exit Sub
        End If
    End If
    
    On Error GoTo ErrH:
    With Application
        .EnableCancelKey = xlErrorHandler
        .EnableEvents = False
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    
    ' restrict our search cells to numeric constants, ignoring formulas
    ' and non-numeric data.
    Set RR = RR.SpecialCells(xlCellTypeConstants, xlNumbers)
    
    ' loop through the cells and test and fix the years
    For Each R In RR
        If Year(DateValue(RR.Text)) Mod 100 >= Year2000 Then
            R.Value = DateSerial(Year(RR) Mod 100 + IIf(Year(R) Mod 100 > Cutoff, 1900, 2000), Month(R), Day(R))
        End If
    Next R

ErrH: ' We're done!
    With Application
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

[#4]

Just remember those settings affect all programs in all of Windows for that user. Unexpected results in other applications could occur.

[#5]

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-96...

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.

[#6]

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.

[#7]

This looks impressive. I wish I was facile with VBA like you. However, I've found over the years I spend so much time debugging without the deep knowledge to be quick with it so I just tend to avoid it as much as possible. I've just adapted to importing and doing Find/Replace.

[#8]

True

[#9]

Good method. It's just a bummer not being able to do everything in one pass.

[#10]

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.

- 

[#11]

@Haytham Amairah 

Windows 7 Pro 64 bit

A software rego .ini file is importing a date ( 2050 ) as 1950, which makes the software unregistered.

Can you think of where in the setup of the PC that might be causing this?

Have checked in Customise Format – Calendar & set to 1952 and 2051 as above, still importing as 1950.

Have re-booted, still no success.

Any suggestions?

Remember, this is not an excel or word file, it is an ini file for software registration.

[#12]

Hi,

Since this issue isn't related to Excel, I suggest you ask another community such as this one.

Regards,

Haytham

@CQPOS