Formatting cell to date does not work

[RuthArg]

I am using MS Excel 2007 Part of Microsoft Office Home and Student 2007. When I format a cell to display the date (no matter which format of date I select) it does not return the correct date format.
Example: I format it to display 3/14/2001. I enter 73011. It should return 7/30/2011. It returns 11/22/2099.
The locale (location) selected is English (United States).
If i type it in using the /'s it comes out as expected. But I don't want to type the /'s
what else can i do to make it format properly?

[johnhutchison]

When you enter 73011 Excel thinks you have just typed in a number, so it correctly formats it as a date. Dates are just numbers, and the number for the date 11/22/2099 is 73011.

Formatting a cell as a date does not remove the need to type the / marks. In Access you can do this with an Input Mask, but I don't believe this can be done in Excel.

[sdckapr]

As johnhutchison mentioned, the behavior you experience is as expected. Without the slashes (/) excel does not know you want the date July 30, 2011 and not the number 73,011. You can create a routine to automatically convert numbers that represent dates without the separators to actual values excel can interpret as a date. For details see MS MVP Chip Pearson's site at http://www.cpearson.com/excel/DateTimeEntry.htm

Steve

[joela44]

By the way, you DO have to type the //s but you only have to type one. If you have the date format set to 7/16/2011 you only need type 7/16 then tab or enter and the cell will properly show 7/16/2011.

Joel

[WebGenii]

And don't forget my favourite Ctrl + ;
This enters the current date.

[joela44]

Well yes, it still does enter the current date, but that's only good today! :-)
Joel

[wesran]

[QUOTE= And don't forget my favourite Ctrl + ;
This enters the current date. l[/QUOTE]

Is there something like tis that works in Open Office Calc.?