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