@ > Home > Contents > Convert to Date, typed in as 14/03/ Excel 5+

Task and Solution:

In some spreadsheet programs you might have to type in 14/3/ for a current year's date. Migrates to Excel often are confused about 14/3 as sufficient entry. The other way the problem solves by its own.

Use following formula to ensure right entries in a date column A:A by converting the wrong and leaving the right ones.

B1: =--SUBSTITUTE(A1&" ","/ ",)
B1: =--SUBSTITUTE(SUBSTITUTE(A1," ",)&" ","/ ",) also corrects wrong spaces like in "14 / 3/".

Copy formula down, then copy contents of formulas over the suspicious values. Keep a copy of the formula in a corner of your sheet with date entries.

The intention is to repair faults already done. In future, better use Data Validity Date before.