Creating custom Excel dates

Emily, a reader who chooses to not provide her last name, is interested in more flexible Excel dating. She writes:

I am wondering if there is a way to turn off auto-formatting in Excel so that, when you enter a date in the format of mm/dd/yyyy, it retains that exact format (including a zero if the day or month is in the single digits… example: 05/07/2006).

I tackle the problem this way:

Enter the dates and let Excel auto-format them any way it likes. When you’ve finished entering the dates, select the area in which they reside and choose Format > Cells.

In the Format Cells window that appears, click the Number tab and then select the Date entry in the Category list. In the Type list choose the 03/14/01 entry.

Now click Custom from the Category list. In the Type field enter mm/dd/yyyy and click OK. The selected cells will adopt the new format so the dates contain not only leading zeroes but the full year as well.

Alternatively, you can select the cells where you intend to enter dates—choose an entire column, for example—and then assign this date scheme to the selected cells before entering them.

Shop Tech Products at Amazon