You probably knew it… when we make a direct reference to a cell, i.e. input =A1 in A2, the cell A2 will then follow the number format of A1.  Put it in other words, if the number format of A1 is Date, then the format of A2 will turn into Date automatically once you make a direct reference to it (by inputting =A1).  As usual, a picture tells thousand words; an animated picture tell even more: 🙂

(Please pay attention to the “Number Format” on the top…)

But did you know that you may make the reference without using source number format?

I couldn’t find a way to turn that option off.  Nevertheless, a formula trick would do.

So what’s the formula trick? 

Ctrl+Shift+Enter.

Try it and you will see.