how do i ensure people use the right date format in excel?

L

lucyh

I am trying to set up a column where people enter the date that they are
attending an event. In order to keep things neat and ensure that we can sort
the column, we need all the dates to be entered in the same format. How on
earth can we do this with Excel?
 
J

JE McGimpsey

You can't affect the way a user enters a value in a cell. However,
there's really no reason to as long as you format the cell the way you
want. What difference does it make if a user enters 1/1/2007 or
1-Jan-2007 as long as the display format is, say 01 Jan 2007.

You *could* use an InputBox or a UserForm to control input if you'd like
to use VBA event macros.
 
H

Harlan Grove

JE McGimpsey said:
You can't affect the way a user enters a value in a cell. However,
there's really no reason to as long as you format the cell the way
you want. What difference does it make if a user enters 1/1/2007
or 1-Jan-2007 as long as the display format is, say 01 Jan 2007. ....
Click to expand...

Reading between the lines, I suspect the OP believes dates are stored
as text, so dates entered as 3/28/2007 could sort after (later than)
04/01/2007. That's not the case. If dates are entered in a valid date
format, they're converted into date values, which are numbers
representing 61 plus the number of days (with time of day as fraction
of day) after 1 Mar 1900 in the 1900 date system or the number of days
from 1 Jan 1904 in the 1904 date system. Either way, 4/1/2007 and
04/01/2007 would both be stored as 39173.

As long as the OP hasn't given these cells the number format Text (and
she should give them a date number format), she can sort them
consistently. Wouldn't hurt to include a data validation rule that the
entered values should be numbers between the earliest and latest
allowed date values.
 
Top