Date Format changes when editing a cell in Excel 2

Question

I have a cell in Excel 2013 formatted as date dd-mm-yyyy.

The content appears correctly but when I edit it the format is different:

This is annoying because it switches the month with day.

How can this be fixed?

Answer 1

Right click on the cell, choose "Format Cells", under Category choose "Custom", then write in "dd-mm-yyyy" and push OK.

If you want to do this for a range of cells, highlight the whole range and then right click on one of the cells in the range and do the same thing.

Answer 2

This is 'by design'. When you edit a cell with a typed (non-formula) date, you are editing the Range.Formula property which in this case is the same as the Range.Value property. The displayed (formatted) date is the Range.Text property. The Range.Formula is the same as that which is seen in the formula bar and the stored format for a typed date is taken from the computer system's regional Short Date setting. The Range.Text property is subject to a formatting mask that changes the way the Range.Value is displayed. Consider it an overlay of the actual value; e.g. if you want to change something, change the something, not a picture of the something.

If you were allowed to edit the displayed text, you would never be able to edit a formula because the values being edited would always be the formula's result (formatted appropriately) and not the formula itself.

If this is really mission critical or just obsessively annoying, change your computer system's Short Date to dd-mm-yyyy and Excel will use that as the base Range.Formula property.

---

_{To be thorough, there is also the Range.Value2 property which in the case of 06-Jan-2016 would be 42375 (number of days past 31-Dec-1899) but that does not really come into play here.}