Numbers Date Conversion 20XX instead of 19XX

This is a problem with the source data, not an import problem. The date was not 2071 in Excel so it won't be in Numbers either.

• 7/14/71 as text in Excel is not a "date" (but if it was it would be 7/14/1971). It will import into Numbers as text 7/14/71. If reformatted as a date in Numbers it will be 7/14/1971.
• The date 7/14/71 (where the year was entered as "71") in Excel is 7/14/1971 and will import to Numbers as the date 7/14/1971.
• The date 7/14/1971 formatted as 7/14/71 in Excel will import as the date 7/14/1971
• The date 7/14/2071 formatted as 7/14/71 in Excel will import as the date 7/14/2071

In a separate column you can add 100 years to the dates to make them 20xx. From there you can Copy then Paste Formula Results back to the original column and delete the column of formulas.

IF B2 has the date 7/14/71

=EDATE(B2,1200)

This adds 1200 months = 100 years.

Hi khromero,

It may be because the year is in the future. Numbers is making a best guess.

A screen shot of your imported dates may help us to see what you see.

Separate the Year, Month, Day using TEXTAFTER, TEXTBEFORE, TEXTBETWEEN

Then recombine using the DATE function, adding 100 to the year.

Formula in C2 is TEXTAFTER(B2,"/",−1)

Formula in D2 is TEXTBEFORE(B2,"/",1)

Formula in E2 is TEXTBETWEEN(B2,"/","/",1,−1)

Formula in F2 is DATE(C2+100,D2,E2)

I formatted column F as YYYY-MM-DD to make the result more "international". You can format the result any way you want.

Regards,

Ian.