How to make DD.MM.YYYY date format readable by Excel

My friend asked me before. How to make 23.05.2013 to 23 May 2013 and readable as date by Excel.
Some of you might say it is easy. Right click> Format Cell>change date format but in reality that is impossible as excel only reads 05/23/2013 format.

Now you will say, just rewrite the date to 05/23/2013. Yes you are right if, we are talking about 1 or 2 dates to work with. But let say we have like 100, 1000 dates. Rewriting the date is definitely not practical.

What you can do is formularize the next cell for it to re-arrange the format from DD.MM.YYYY to MM/DD/YYYY.

 Formularize each column B,C,D to extract Month, Day and Year:

Result:

Okay, now to consolidate the back the Month, Day and Year to form a Date.


Consolidate Formula:

Result in Column E:

Then you might think, pretty tedious just to convert a date? There is an easier way which you can consolidate everything in 1 formula

Consolidate in 1 long formula:

Result in Column F: 

Now congratulate yourself as you have a Date! Now to convert it do 23 May 2013 format.(To bad you still cannot use the Right click> Format Cell>change date format way. You can try but I assure you, you will not succeed).


 Use 'text' formula to change the format of the date:

Result:

In the text formula, you can try and change the 'M' from 4 M's to 1,2,3 or 4 and see the difference. ;)
Not only that, you can play around with DD MMM YYYY sequence and can even add dot(.) or hyphen (-) or slash (/).


Final Result:








Few Excel function that you have learnt today are:

1.Mid - Which return middle character(s) from a string/text
2.Left - Which return most left character(s) from a string/text
3.Right - Which return most right character(s) from a string/text
4.Text - Which change the format of a text

Here's the link to the excel file:
https://docs.google.com/file/d/0B22og94RNnAKNE5xaTAwS0FVeTA/edit?usp=sharing

Okay, Happy trying then. Hope these help!! :)