Entering dates in Excel

muchiku 

It is possible to enter a datelike value as in your example, and have Excel store it as a date with the formatting your example specified. However, it's not accomplished by the method Nikolino specified, unless you/your users can mentally (or otherwise) convert their intended date into the number of days since ~Dec. 31, 1899 (as is obliquely mentioned in his "Remember…" paragraph) before typing.  The entry of 70220 will result in a display of 4/01/92.  So that technique does not seem worth consideration.

Excel always checks the user input to decide: Is this input a formula, a number, a date, or just text? There are no indications in the five digits in your example to Excel to tell it "I mean this to be a date", so it will (initially) store a number (unless the cell is already defined to store Text).  However, Excel also allows you to write an event handler (specifically, for the Worksheet_Change event), which is automatically invoked whenever the content of any cell in that worksheet is changed. You could write that event handler to do the following:

1. decide whether to make the following conversion from a number to a date (presumably based on what the cell address is) or leave it as-is;
2. break the five or six digits that were entered into the three component parts;
3. construct a Date value from those parts. There will have to be logic that decides whether, e.g., a year component of 60 means 1960 or 2060, and whether the "middle" component represents the day (as in US common usage) or the month (as in European common usage).
4. store that constructed Date value (which internally is just a positive number) back into the cell; and
5. change the display-formatting code to show the value in the m/dd/yy format, which is what I presume you intended. (This last step is part of what Nikolino's response said, but would be automatic, not manual.)

The event handler needs to be written to also handle cases where non-datelike values are entered (whether a formula or text or just an invalid datelike value). You'll have the design choice of whether to reject the data (store and show a #VALUE! error, e.g.) or allow the initial cell content to remain.

The event handler can be written in VBA, but by doing so (in a "macro-enabled workbook"), realize that doing so can expose you/your users to additional malware threats.  Someone else may correct me, but I don't believe that event handlers can be written using (safer) Office Script.  So think carefully before you go this route: Is it worth this effort and risk to reduce my typing input by two characters per date?