Text Date Conversion

[Guest]

How can I change the way that excel inteprets "dates entered as text values"?
Maturity dates on 30-years bonds are incorrectly interpreted, even after I
change the Regional Options on the Control Panel.

 

[Guest]

It would help to know how they are entered.

 

[Guest]

The date is imbedded in the middle of a text cell in "mm/dd/yy" format.

 

[Ron Rosenfeld]

How can I change the way that excel inteprets "dates entered as text values"?
Maturity dates on 30-years bonds are incorrectly interpreted, even after I
change the Regional Options on the Control Panel.

It sure would save a lot of troubleshooting time if you shared with us what you
changed in Regional Options; what you are entering; what you get and what you
expect to get.

My first suggestion would be to go to Regional Options and make the appropriate
change.
--ron

 

[Guest]

What else is around it? It's hard to know what to tell you to do without
more details.

 

[David Biddulph]

If you want Excel to interpret it as a date, you need to extract the
relevant string from the middle of the rest of your text.

 

[Guest]

Barb -

The original cell contents are: "UT076 7.000 DUE12/15/31"

I then use the MID Function in another cell to extract the sub-string:
"12/15/31"

In yet another cell, I add "0" to convert the text string to EXCEL Date
Format.

The resulting Excel Date is 12/15/1931 instead of the desired 12/15/2031.

I have followed the instructions in EXCEL Online Help labelled: "How Excel
interprets two-digit years" and have changed my settings so that yy=30 to 39
should be interpreted as 2030 to 2039.

This does correct NUMERIC entry errors, but it did not help the senario above.

Chris

 

[Guest]

Ron -

I just updated the settings as directed in the EXCEL Online Help screen:
"How Excel interprets two-digit years". i changed the settings so yy = "30"
to "39" is interpreted by Excel as 2030 to 2039.

This change did correct NUMERIC entries. However, is I add a Zero to a cell
containing the text string "12/31/30" the resulting EXCEL Date Value in
12/31/1930, but the desired 12/31/2030.

Chris

 

[Ron Rosenfeld]

Ron -

I just updated the settings as directed in the EXCEL Online Help screen:
"How Excel interprets two-digit years". i changed the settings so yy = "30"
to "39" is interpreted by Excel as 2030 to 2039.

This change did correct NUMERIC entries. However, is I add a Zero to a cell
containing the text string "12/31/30" the resulting EXCEL Date Value in
12/31/1930, but the desired 12/31/2030.

Chris

That is fascinating. And I confirm that it works on my machine (Windows XP
Pro, MS Excel 2003) just as you describe.

In addition, using the formula =DATEVALUE("12/31/30") also converts to
12/31/1930 even though the regional windows setting are set to interpret two
digit years a being between 1940-2039.

Unless someone comes up with a better idea, my suggestion would be to use a
formula to do the conversion.

Extract using the MID function, as you have
Test the year
If the year is less than today's year
Add 100 to the year.

e.g. with the extracted and converted value in c22:


=IF(YEAR(C22)<YEAR(TODAY()),DATE(YEAR(C22)+100,MONTH(C22),DAY(C22)),C22)

You could replace C22 with your MID and Add 0 formula.
--ron

 

[Fred Smith]

I too had problems with Excel not converting the century correctly. As your bond
maturities will always be in the future, one workaround is to enhance your MID
formula to insert '20' before the year. Something like:

=mid(a1,15,6)&"20"&mid(a1,21,2)+0