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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 04:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com