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?

muchiku 

It is possible in Excel to enter a date in a cell using a different format and have it automatically formatted into the desired format. To achieve this, you can use Excel's custom number formatting feature.

Here's how you can do it:

1. Select the cell where you want to enter the date.
2. Right-click on the selected cell and choose "Format Cells" from the context menu. Alternatively, you can press Ctrl + 1 to open the Format Cells dialog box.
3. In the Format Cells dialog box, go to the "Number" tab.
4. Select "Custom" from the Category list.
5. In the "Type" field, enter the following format code: m/dd/yy This format code specifies that the date should be displayed as "month/day/year".
6. Click on the "OK" button.

Now, when you enter the date as 70220 in the cell and press Enter or move to another cell, Excel will automatically format it as 7/02/20 according to the custom format you specified.

Remember that Excel treats dates as serial numbers, with January 1, 1900, being represented by the number 1. So, when you enter 70220, Excel interprets it as the 70,220th day since January 1, 1900, and then formats it according to the custom format you applied.

Note: The above instructions assume that you are using the U.S. date format (month/day/year). If you are using a different date format, adjust the format code accordingly. For example, if you prefer the format as day/month/year, you can use the format code d/mm/yy.