Nice one beni..

Hi VKS, DATEVALUE is a good option but since you need to have 2012 as output, you need to subtract 1 from the year because DATEVALUE will give you 41286 which is 12-Jan-2013 and year is 2013 here

I have observed, Excel uses current year always, when it finds that year part is missing in any date
So basically if you enter two parts only out of DATEs requirement as 3 parts (Day Month Year), excel considers those two parts as "day" and "month" only and never assumes that you have entered "Year"

Regards,
DILIPandey
<click on below * if this helps>