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.

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


--
Pecoflyer
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24064

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


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24064

On Sat, 1 Nov 2008 10:16:00 -0700, Hawk wrote:

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

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)

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Sat, 1 Nov 2008 10:16:00 -0700, Hawk
wrote:

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

Hi,

And if you want a still shorter formula

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


--
Thanks,
Shane Devenshire


"Rick Rothstein" wrote:

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)

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Sat, 1 Nov 2008 10:16:00 -0700, Hawk
wrote:

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

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

"ShaneDevenshire" wrote in
message ...
Hi,

And if you want a still shorter formula

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


--
Thanks,
Shane Devenshire


"Rick Rothstein" wrote:

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)

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Sat, 1 Nov 2008 10:16:00 -0700, Hawk

wrote:

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

On Sun, 2 Nov 2008 01:19:57 -0500, "Rick Rothstein"
wrote:

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)

--
Rick (MVP - Excel)

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