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

=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
>
>

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
>
>

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
>
>

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 <Cr...@discussions.microsoft.com> 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