Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mileage Claim Formula | New Users to Excel | |||
format cell to know number of another cell automatically | Excel Worksheet Functions | |||
Date Format Problems?? | Excel Discussion (Misc queries) | |||
date format in excel not in line with control panel regional setti | Excel Discussion (Misc queries) | |||
Imported Date & Time format with calcs. managed in excel from imrp | Excel Worksheet Functions |