Hello,
1) Understand what a date is (It is an integer number)
2) Input of data in general got nothing to do with the format of a cell (some exceptions exist from this rule)
3) Input of a date requires a conversion of what you enter into a number (see 1). This requires to follow a date acceptance pattern; i.e. which textual input will be recognized as a date and correctly converted into a number. This date acceptance pattern highly depends on your language settings (see Tools -> Options -> Language Settings -> Languages -> Option: Date acceptance patterns
). The only universal date acceptance pattern is: YYYY-MM-DD
(ISO 8601). This pattern will always be recognized and correctly turned into a number
4) Now and only now, formatting comes on to the scene. Formatting provides the presentation of that recognized numerical value. Formatting provides a rule how to show an the given integer value to the user of the application.
Having said this:
- Remove any content of a currently formatted cell
- Remove any format
- Enter
1999-01-01
(or in any date format that matches your date acceptance pattern) - Now format the cell how you want to appear it.
Hope that helps.