Automatically format number as date
 

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?

Automatically format number as date
 

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.



Teri wrote:

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?

--

Dave Peterson

Automatically format number as date
 

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.

"Dave Peterson" wrote:

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.



Teri wrote:

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?

--

Dave Peterson

Automatically format number as date
 

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.

Teri wrote:

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.

"Dave Peterson" wrote:

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.



Teri wrote:

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?

--

Dave Peterson


--

Dave Peterson

Automatically format number as date
 

Teri

If you want it done while entering see Chip Pearson's site for code.

http://www.cpearson.com/excel/DateTimeEntry.htm

If you want them to enter the numbers and deal with them later, use

DataText to ColumnsNextNextColumn Data FormatDateMDYFinish


Gord Dibben MS Excel MVP

On Tue, 3 Oct 2006 13:41:02 -0700, Teri wrote:

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?