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:

How to color few cells with the same color without clicking the fill color button

Hi! Okay today I am going to reveal the fastest way to color cells with the same color without click the fill color button and the cells back and forth.

Okay now. Let say you want to color all of number 1 to yellow.



You can start by coloring the first number 1 as usual.

For the rest of number one, instead of clicking back and forth the cell and the fill color button, you can just activate the cell that you want the color to be changed to yellow and effortlessly press F4.

Easy! :). Actually F4 is a shortcut key to copy last action made by you. Eg: If let say the last action was deleting Row. You can easily delete another rows by selecting the row and pressing F4. Without having to right click and select delete row.

Have fun trying. :)