Pasting two digit year date into Excel (08/25/25) returns (08/25/2025) in cell

BostonMA
BostonMA used Ask the Experts™
on
I have a notepad of unformatted birthdates.  They look like:

08/25/25
10/05/43
01/15/29

If I highlight the first date and past it into excel, it returns 08/25/2025.  I have already followed the instructions from microsoft about setting my regional language settings, found here:

http://office.microsoft.com/en-us/excel-help/change-the-date-system-format-or-two-digit-year-interpretation-HP010054141.aspx

I've attached a screen shot of what the regional settings now look like.  I restarted my computer after I made the change.

My question is, how do I change the settings so that excel will interpret the date as 19XX instead of 20XX?
Date-setting.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
if you want all 2 digit year values to start with '19' when creating the 4 digit  year. Use '1999' in above regional settings instead of '2011'.
Ritesh RatnaBusiness Analyst/SharePoint Consultant

Commented:
Are you using the Date Range as in your Screen shot?

If Yes, then I guess this is where the problem lies.

I would suggest you to use the date range as -- 1900 and 1999.

Try this. I tested it in my system it works.

Author

Commented:
That didnt work.

I changed the setting to go from 1900 to 1999 but it still pastes 01/01/28 as 1/1/2028 into excel 2007.

Any other ideas?
settings-2.JPG
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Note that if I type 01/01/28 into a cell, it correctly understands it as 1/1/1928.
Ritesh RatnaBusiness Analyst/SharePoint Consultant

Commented:
BostonMA,

From your last note as above, should I understand that the solution has worked and your problem is resolved?

Author

Commented:
The problem is not resolved.  The problem is:

If I type into the cell, Excel correctly interprets 01/01/28 as 01/01/1928. (This is good).

If I copy the text '01/01/28' and paste it into Excel,  Excel interprets it as 01/01/2028. (This is bad).


Any ideas how to paste it into excel and have it understand the date as if I was pasting it in? Note that if I do a 'paste special' all I get for choices is 'Unicode Text' or 'Text' - I dont get the normal paste special window with all the formatting choices.
melli111SharePoint Administrator / Developer

Commented:
Highlight the cells that you want to paste these dates into.  Right click and choose Format Cells.  In the Number tab, choose Custom.  In the type enter the following:
mm/dd/"19"yy
Click OK, and paste away  :)
Rather than copy and paste, can you import? I know that Excel has an import feature for text. All you should have to do is Open the *.txt file and Excel will bring it in. At that point you should be able to parse your data - text to columns. You might need to format your text file a little so that you have commas or some other identifier to separate the date from surrounding text.

Hope that helps.
Dawn Bleuel
Word MVP
Top Expert 2014

Commented:
If this is a one-time data import, allow the dates to be in the future and then change any of them that exist in the future.

Author

Commented:
See screenshot.  So basically you have formatted the value to look like 1929.  I want the actual value to be 1929.   I'm concerned that if I use this file as a source file for anything else that the destination will interpret the cell as 2029.
formula-bar-is-different-than-ce.JPG
Stacy BrownSenior Applications Administrator

Commented:
You need to change the date format of the cells in Excel.

--Select the cells where you will be pasting this information
--Right click and choose Format Cells
--Select Date from the list on the left
--Select the 3/14/01 option
--Click OK
--Now copy and paste your information from the text editor into the formatted cell(s) in Excel and it will retain the correct formatting
2010-10-29-03.jpg

Author

Commented:
melli111:  See my above response with screen shot.

dlc110161:  I tried you suggestion of importing the data and it brings 10/12/29 in as 10/12/2029.

aikimark: This is what I  have been doing, but I get really big files where there will be 5-10k birthdays in this format where most are before 1930 - so manually changing is not my preference (although i did figure out a way to do it with formulas i feel that there must be an easier way).
senior developer
Top Expert 2005
Commented:
That's the why Microsoft has created excel to work:

http://support.microsoft.com/?id=230931

"Note While you can change the way the system interprets two-digit dates under the Control Panel in Regional and Language Settings, Excel only uses that setting when you enter dates manually. If you import a text file or automate date entries by using Microsoft Visual Basic for Applications (VBA), the fixed 2029 rule is in effect."

The best I can recommend is a macro that runs after the data is loaded.  In my example, the date column is "D" and data begins in row 2.  This will loop through the entire range of data and if the date is in the future, it sets it back 100 years.

Sub FixDate()
    Dim vRange As Range
    Dim vCell As Range

    Set vRange = Range("D2")
    If Range("D3") <> "" Then
        Set vRange = Range(vRange, vRange.End(xlDown))
    End If
   
    For Each vCell In vRange
        If DateValue(vCell.Value) > Date Then
            vCell.Value = DateAdd("yyyy", -100, DateValue(vCell.Value))
        End If
    Next
End Sub
try using "paste special" and select "values"

Author

Commented:
Excellent right on the money.
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database Architect

Commented:
Excel doesn't interpret dates well at the best of times. What you can do is:

Clear All (Edit...Clear...All) from the target cells (say column A)
Paste your dates into Excel from notepad.
Click the Paste Options drop-down and select Text Import Wizard, and in step 3 - make sure that the "Text" option is selected.
Use a formula such as the following to do the conversion correctly for you (this assumes that you have pasted the dates into column A.

=DATE(RIGHT(A1,2), LEFT(A1,2), MID(A1,4,2))

Use Auto Fill to fill down your date column.

Hope this helps
melli111SharePoint Administrator / Developer

Commented:
Hi Boston, I just tested it and you're right.. another program will interpret it incorrectly (as 2025).  jrb1's solution is probably the way to go...
melli111SharePoint Administrator / Developer

Commented:
Sorry, I didn't notice that you accepted his solution until after I posted  :)  Glad you got your answer
Years have four digits. You should always use four digits. Period. Using a 2 digit shortcut is simply not worth the effort you have to put in to get around the problems it creates.

Were you not around during the Y2K fiasco? :-)  I'm sure that's not the solution you want to hear, but it *is* the solution IMHO.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial