Automatically format number as date

T

Teri

I am creating a spreadsheet for non-Excel users. If I tell them to enter a
date, I'll see it fifteen different ways. I want to instruct them to enter
the date as xxxxxx. In other words, October 3, 2006 would be entered at
100306, but I want it to display 10/03/06. How can I do this?
 
D

Dave Peterson

Maybe you could just format the cell the way you like (mm/dd/yy).

Then use data|validation to make sure that they enter a date.
 
T

Teri

I would do that, but they'll find all kinds of ways to enter a date and then
will complain if we ask them to enter the / / . I thought there was a way to
enter six numbers and Excel would format for me.
 
D

Dave Peterson

If they enter a date--any input that excel can figure out is a date (even
October 3, 2006), then the entry will be accepted by the Data|validation
rules--if it's not something that looks like a date (to excel), then excel won't
accept it.

And since it's a real date, the number format will change the way the value is
displayed.

You may want to try it.
 
Top