function to force date?
 

Hello Experts,

I have an interactive spreadsheet in which users enter data into
approx. 5 columns.

In one of the columns, I would like users to enter the date in
yyyymmdd format, e.g., 20070226. I know they won't get it right so I
would like to preemptively stop it.

I've used data validation on some of the other columns, but have found
it not applicable with the scenario.

It would be nice to code the column with some sort of substring, e.g.,
first four characters =2007; next two between 01 and 12; next two
between 01 and 31-knowing, however, this would not completely solve my
problem; it would help it; however.

Any thoughts?

alex

function to force date?
 

My advice would be to create a custom format for that cell that would show
the date in the format you are looking for. Enter yyyymmdd as the custom
format. Then you can add data validation to ensure that the users enter
dates. It doesn't matter how the user enters the date. However, they enter
the date it will be displayed according to the format you have set (yyyymmdd).

However, if you really need them to enter the date in the yymmdd format as
well as displaying it in that format, the only way to do that is through a
macro.
Or, you could split the date up into 3 separate parts/cells (year, month,
and day) and apply data validation on those cells.

Again, my advice would be to use the cell format to display the date the way
you want. Who cares how the users enter the date.

Hope that helps.
Bill Horton

"alex" wrote:

Hello Experts,

I have an interactive spreadsheet in which users enter data into
approx. 5 columns.

In one of the columns, I would like users to enter the date in
yyyymmdd format, e.g., 20070226. I know they won't get it right so I
would like to preemptively stop it.

I've used data validation on some of the other columns, but have found
it not applicable with the scenario.

It would be nice to code the column with some sort of substring, e.g.,
first four characters =2007; next two between 01 and 12; next two
between 01 and 31-knowing, however, this would not completely solve my
problem; it would help it; however.

Any thoughts?

alex

function to force date?
 

You could also follow Ron de Bruin's tips for placing a pop-up calendar on
your worksheet

http://www.rondebruin.nl/calendar.htm

"alex" wrote:

Hello Experts,

I have an interactive spreadsheet in which users enter data into
approx. 5 columns.

In one of the columns, I would like users to enter the date in
yyyymmdd format, e.g., 20070226. I know they won't get it right so I
would like to preemptively stop it.

I've used data validation on some of the other columns, but have found
it not applicable with the scenario.

It would be nice to code the column with some sort of substring, e.g.,
first four characters =2007; next two between 01 and 12; next two
between 01 and 31-knowing, however, this would not completely solve my
problem; it would help it; however.

Any thoughts?

alex