two digit year imports wrong into mm-yy formatted field

  • Thread starter Thread starter Gozer
  • Start date Start date
G

Gozer

We export information from our AS/400 database into excel. One of the
exported fields is in the format of mm-yy. The field in Excel is also
formatted as mm-yy, but in cases where the year begins with zero,
excel is substituting the current year for the imported information.
For example, the dates:

02-03
04-99
11-00
09-02

would end up as

02-04
04-99
11-04
09-04

Any suggestions or solutions would be greatly appreciated.
 
Hi Gozer
I believe that excel is interpreting your 2003 dates as mm-dd instead of mm-yy. The formatting of the cell only effects the way the data is displayed, it doesn't effect the way excel interprets the input. 02-03 could be either February third of February 2003. When the year is left out, which is what Excel thinks is happening, the current year is used

You may need to import this field as text

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Gozer wrote: ----

We export information from our AS/400 database into excel. One of th
exported fields is in the format of mm-yy. The field in Excel is als
formatted as mm-yy, but in cases where the year begins with zero
excel is substituting the current year for the imported information.
For example, the dates

02-0
04-9
11-0
09-02

would end up as

02-0
04-9
11-0
09-0

Any suggestions or solutions would be greatly appreciated
 
Hi Gozer!

Re-import your data as text. Then you can convert the text to your
required dates using:

=DATE(IF(--RIGHT(A1,2)<30,--RIGHT(A1,2)+2000,--RIGHT(A1,2)+1900),--LEF
T(A1,2),1)
Format mm-yy



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top