| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
converting text into years
Hi,
I have one column of text imported into excel that looks like column A data...I want to use a function that would give me whats in Column B, that is, the year of the last two characters in col A...I tried the year function and it worked for 1996-2000, but not for years 2001-2008...it gave me 2009 as a result. Any help appreciated very much Craig col A I want Col B to look like this: 01-00 2000 01-00 2000 01-00 2000 01-03 2003 01-01 2001 01-02 2002 01-07 2007 01-04 2004 01-08 2008 01-99 1999 01-98 1998 01-97 1997 01-96 1996 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting text into years
=VALUE(IF(VALUE(RIGHT(A1,2))9,"19","20")&RIGHT(A1 ,2))
I'm assuming you have not "future" dates, so the latest year this function will return is 2009. If 2009 is not an option, change the comparison to "8" -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Craig" wrote: Hi, I have one column of text imported into excel that looks like column A data...I want to use a function that would give me whats in Column B, that is, the year of the last two characters in col A...I tried the year function and it worked for 1996-2000, but not for years 2001-2008...it gave me 2009 as a result. Any help appreciated very much Craig col A I want Col B to look like this: 01-00 2000 01-00 2000 01-00 2000 01-03 2003 01-01 2001 01-02 2002 01-07 2007 01-04 2004 01-08 2008 01-99 1999 01-98 1998 01-97 1997 01-96 1996 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting text into years
Hi,
Try =YEAR(LEFT(A5,5)) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Craig" wrote: Hi, I have one column of text imported into excel that looks like column A data...I want to use a function that would give me whats in Column B, that is, the year of the last two characters in col A...I tried the year function and it worked for 1996-2000, but not for years 2001-2008...it gave me 2009 as a result. Any help appreciated very much Craig col A I want Col B to look like this: 01-00 2000 01-00 2000 01-00 2000 01-03 2003 01-01 2001 01-02 2002 01-07 2007 01-04 2004 01-08 2008 01-99 1999 01-98 1998 01-97 1997 01-96 1996 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting text into years
If the data goes from 1996 thru 2009 then in B1 enter:
=IF(--RIGHT(A1,2)<10,2000+RIGHT(A1,2),1900+RIGHT(A1,2)) -- Gary''s Student - gsnu200834 "Craig" wrote: Hi, I have one column of text imported into excel that looks like column A data...I want to use a function that would give me whats in Column B, that is, the year of the last two characters in col A...I tried the year function and it worked for 1996-2000, but not for years 2001-2008...it gave me 2009 as a result. Any help appreciated very much Craig col A I want Col B to look like this: 01-00 2000 01-00 2000 01-00 2000 01-03 2003 01-01 2001 01-02 2002 01-07 2007 01-04 2004 01-08 2008 01-99 1999 01-98 1998 01-97 1997 01-96 1996 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
converting text into years
Assuming that those are text values, and that the span of years is
from 1931 to 2030, put this formula in B1: =IF(--RIGHT(A1,2)<=30,2000+RIGHT(A1,2),1900+RIGHT(A1,2)) and copy down as required. Hope this helps. Pete On Feb 18, 4:25*pm, Craig wrote: Hi, I have one column of text imported into excel that looks like column A data...I want to use a function that would give me whats in Column B, that is, the year of the last two characters in col A...I tried the year function and it worked for 1996-2000, but not for years 2001-2008...it gave me 2009 as a result. Any help appreciated very much Craig col A * I want Col B to look like this: 01-00 * 2000 01-00 * 2000 01-00 * 2000 01-03 * 2003 01-01 * 2001 01-02 * 2002 01-07 * 2007 01-04 * 2004 01-08 * 2008 01-99 * 1999 01-98 * 1998 01-97 * 1997 01-96 * 1996 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem With Converting Days Months And Years | Excel Discussion (Misc queries) | |||
| Probem Of Converting Days Months And Years | Excel Discussion (Misc queries) | |||
| CONVERTING TIME (DAYS) TO ONTHS,YEARS, ETC. | Excel Worksheet Functions | |||
| Converting number of days to years and months | Excel Worksheet Functions | |||
| Converting months to years | Excel Worksheet Functions | |||
Linear Mode
