Excel keeps changing the date although I specified a format explicitly

Question

I have a cell that I specifically defined it's format as date in the custom format dd/mm/yyyy and when I put a value in the cell it disregards this and does whatever it likes, this is very frustrating especially because the format chosen is US so it changes the meaning of dates and is not useful at all anywhere in the world.

Answer 1

Generally, cell formats determine how Excel displays a numeric value, not how it interprets data entry.

Excel's interpretation of dates depends on the date formats specified in the Regional and Languages control panel.

So if the R&L short date format is m/d/yyyy, you must enter dates in that form (although Excel also permits m/d/yy as well as other variations).  Then it is displayed as dd/mm/yyyy, according to your Custom format.

For another example, if we format a cell to display time as [m]:ss (min and secs), then enter 1:23, Excel displays 83:00 because 1:23 is interpreted as h:m.

---

PS....  Excel does sometimes change cell formats despite our explicit settings.  Sometimes it is a defect (e.g. when re-opening a file).  Sometimes it is just Excel's eccentric nature (changing Currency to Accounting when re-entering a formula using a financial function).

But in your case, I suspect the cell format per se is not changed (select the cell, right-click and click Format Cells > Number (tab)).  Instead, I suspect your dd/mm/yyyy date entry is interpreted as type text (different from format Text) because your dd is larger than 12, the max mm.  You can confirm the data type by using the ISTEXT function.

Answer 2

Ahh so I need to input the data in my region and language format, thanks for the help.