sunny
my thread is
quote
suppose in my regions it is entered as in A1
15.11.2012 which you want to mean 15th November 2012
such entry will not be date but only a string
to convert in B1 (any empty cell) enter this formula
=(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))+0
unquote
you entered date asz 15.11.2012(15th november 2012) whereas you should have entered as 11/15/12
having entered as 15.11.12 which is a string at least in my computer
I have to convert to date as 15th november 2012
so I take first mid(a1,4,2)
take the fourth item and the lengthof 2
fourth item is 1 and length2 menas 11
then I add a slash "/"
and then add left two items that is 15 and the a slash
and then last four items that is 2013.
so it will be 11/15/2012
but as you used mid left etc the final result that is 11/15/12 is also a string
something like '1234
to make it a date I have to add a zero to the whole text
then you get a date normally as a five figure number
dates are always taken as a number in excel
so you to format as date in any format
dd-mmm-yyyyy that 15-nov-2012
This formula is not working in the situation like 4/08/2014