Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Date Conversion
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Date Conversion
It would help to know how they are entered.
"CPodd" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Date Conversion
The date is imbedded in the middle of a text cell in "mm/dd/yy" format.
"Barb Reinhardt" wrote: It would help to know how they are entered. "CPodd" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Date Conversion
On Tue, 8 May 2007 10:18:00 -0700, CPodd
wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Date Conversion
What else is around it? It's hard to know what to tell you to do without
more details. "CPodd" wrote: The date is imbedded in the middle of a text cell in "mm/dd/yy" format. "Barb Reinhardt" wrote: It would help to know how they are entered. "CPodd" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Date Conversion
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. -- David Biddulph "CPodd" wrote in message ... The date is imbedded in the middle of a text cell in "mm/dd/yy" format. "Barb Reinhardt" wrote: It would help to know how they are entered. "CPodd" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Date Conversion
Barb -
The original cell contents a "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 "Barb Reinhardt" wrote: What else is around it? It's hard to know what to tell you to do without more details. "CPodd" wrote: The date is imbedded in the middle of a text cell in "mm/dd/yy" format. "Barb Reinhardt" wrote: It would help to know how they are entered. "CPodd" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Date Conversion
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" wrote: On Tue, 8 May 2007 10:18:00 -0700, CPodd wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Date Conversion
On Tue, 8 May 2007 10:56:02 -0700, CPodd
wrote: 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,MON TH(C22),DAY(C22)),C22) You could replace C22 with your MID and Add 0 formula. --ron |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Date Conversion
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 -- Regards, Fred "Ron Rosenfeld" wrote in message ... On Tue, 8 May 2007 10:56:02 -0700, CPodd wrote: 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,MON TH(C22),DAY(C22)),C22) You could replace C22 with your MID and Add 0 formula. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
import /paste -stop automatic conversion of text 05-12345 to date | Excel Discussion (Misc queries) | |||
Date conversion | Excel Discussion (Misc queries) | |||
Date conversion | Excel Worksheet Functions | |||
Date Conversion | Excel Worksheet Functions | |||
date conversion | Excel Worksheet Functions |