04-27-2017 04:45 PM
04-27-2017 04:45 PM
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?
04-27-2017 09:44 PM - edited 04-27-2017 09:47 PMSolution
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
04-27-2017 10:51 PM
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, email@example.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
04-27-2017 10:55 PM
04-28-2017 04:42 PM - edited 04-28-2017 04:47 PM
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.
05-07-2017 12:18 PM
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.
05-07-2017 12:22 PM
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.
05-08-2017 01:41 AM
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.
05-13-2019 03:25 PM
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.
Remember, this is not an excel or word file, it is an ini file for software registration.