Converting a cell value

[Hawk]

I have a worksheet that is imported from another application. One column
involves a Model Year of a vehicle and the last two digits of the year is
imported and leading zeros are deleted. That means that I have a column with
single digit numbers 0 through 8 representing 2000 through 2008. I also have
a few two digit numbers...98, 99, 93, etc., representing 1998, 1999, 1993
model years of vehicles. I am trying to come up with a formula to convert
this column of 1 or two digit numbers to the correct model year. If the cell
has a 0 through 9 in it, I want it to convert to 2000 to 2009. If it has a
two digit number of anykind (XX), I want it to convert to 19XX. I just can't
figure out what the formula would be to do this and all kinds of searching
hasn't led me anywhere. Thanks.

 

[Pecoflyer]

Hi, how are your digits formatted, text or numbers?

 

[shg]

You can use =1900+(A1<=9)*100+A1, and change the 9 to whatever number
you want to break between 1900 and 2000.

 

[Ron Rosenfeld]

I have a worksheet that is imported from another application. One column
involves a Model Year of a vehicle and the last two digits of the year is
imported and leading zeros are deleted. That means that I have a column with
single digit numbers 0 through 8 representing 2000 through 2008. I also have
a few two digit numbers...98, 99, 93, etc., representing 1998, 1999, 1993
model years of vehicles. I am trying to come up with a formula to convert
this column of 1 or two digit numbers to the correct model year. If the cell
has a 0 through 9 in it, I want it to convert to 2000 to 2009. If it has a
two digit number of anykind (XX), I want it to convert to 19XX. I just can't
figure out what the formula would be to do this and all kinds of searching
hasn't led me anywhere. Thanks.

=A1+1900+100*(A1<30)

So any two digit number less than 30 is presumed to represent a date in this
century; if it is 30-99 it represents a date in the last century.
--ron

 

[Rick Rothstein]

If we assume the OP has not modified the normal break point for determining
the century in his Windows regional setting (default is the 30 you are using
in your formula), then this shorter formula can be used...

=YEAR("1/1/"&A1)

 

[ShaneDevenshire]

Hi,

And if you want a still shorter formula

=--("1/1/"&A1)

 

[David Biddulph]

.... though of course that will give the date, rather than the year.

 

[Ron Rosenfeld]

If we assume the OP has not modified the normal break point for determining
the century in his Windows regional setting (default is the 30 you are using
in your formula), then this shorter formula can be used...

=YEAR("1/1/"&A1)

True. But I wonder which would execute more quickly.
--ron