Convert year

[JaR]

When I import automotive data from another database which shows model year
in two-digit format, into excel, the model year imports as follows; 98 = 98,
but 01 = 1 02 = 2, etc. I have installed the date migration tool, but I
still can't seem to convert the year into a four digit format. Is there a
way to convert a column of one or two digit numbers to four digit year?

Thanx for any help.

 

[Jean Ruch]

When I import automotive data from another database which shows model year
in two-digit format, into excel, the model year imports as follows; 98 = 98,
but 01 = 1 02 = 2, etc. I have installed the date migration tool, but I
still can't seem to convert the year into a four digit format. Is there a
way to convert a column of one or two digit numbers to four digit year?

Thanx for any help.

Hello J.R.,

If your column contains only Years, try
Format / Cells / Numbers / custom defined and use YYYY
For homogeneous two digits YY
Complete Dates as YYYY MM DD or similar

regards

Jean

 

[JohnI in Brisbane]

JaR,

Try this formula-

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

It assumes any years less than "30" are 2000 or more.

regards,

JohnI

 

[JaR]

Hello J.R.,

If your column contains only Years, try
Format / Cells / Numbers / custom defined and use YYYY
For homogeneous two digits YY
Complete Dates as YYYY MM DD or similar

regards

Hi Jean;

I'm using excel 97 and when I use the YY or YYYY it just converts all to
either 00 or 1900. Is there something else I need to do?

Thanks

J.R.

 

[JaR]

JaR,

Try this formula-

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

It assumes any years less than "30" are 2000 or more.

Thanks Johnl, that worked perfectly. Wish I could buy ya a Beverage of
Choice.

J.R. in Spokane,WA ;-)

 

[Niek Otten]

In an empty cell, enter the number 1900. Edit>Copy. Select the "years".
Edit>Paste Special, check "Add"

 

[Earl Kiosterud]

J.R.,

It's Y2K revisited!

A value like 01 needs to be 2001, presumably, but 98 needs to be 1998. So
we need the classic "windowing" function to decide which century to apply to
your 2-digit years. In another column, use:

=DATEVALUE("1/1/"&A2)

The 2000's window ends with 29 (yields 2029), and the 1900's window starts
with 30 (yields 1930). The formula will give an actual date, like 1/1/1998,
but you can format it for just the year (Format - Cells - Number - Custom:
yyyy). To yield a plain number, like 1998 or 2001 (not a formattable
date-serial number), use:

=YEAR(DATEVALUE("1/1/"&A6))

Earl Kiosterud
mvpearl omitthisword at verizon period net