Excel formula: input just 4 digits excel converts to date format dd/mm/yyyy -

Excel formula: input just 4 digits excel converts to date format dd/mm/yyyy -

following on before question: excel - convert date yymmdd dd/mm/yy

i input 4 digits , formula convert date.

for example, if come in value 1207, formula convert 12/07/2014 if input 0311 converts 31/1/2014.

i couldn't work out how date conversion when input date starting "0" such 0711.

the formula i'm using:

=left(a2,2)&"/"&mid(a2,3,4)&"/2014"&right(a2,0)

see wrong output highlighted below:

input date converted 1112 11/12/2014 1503 15/03/2014 2407 24/07/2014 2912 29/12/2014 0712 71/2/2014 <~~~~~~ should 07/12/2014 0311 31/1/2014 <~~~~~~ should 03/11/2014

you can utilize formula suggested jerry. have same formula:

=date(2014,right(a2,2),left(a2,2))

but, need create sure inputs text. 0712 should displayed 0712 in excel. i.e maintain input four digits above formula work. can utilize apostrophe before 0712 convert text, come in '0712 input.

excel excel-formula