I have several workbooks that I distribute where I have formatted the date
columns to display as dd-mmm-yyyy. The users fill in these dates. This is
the way I want them formatted, because that is the way they ultimately need
to be entered into our computer system.

The problem is, if the user looks at the instructions to enter as
dd-mmm-yyyy and mearly thinks, "Ok -- day, monty, year" and enters the 7th of
January, 2005 as 7-1-2005, it will display as 01-Jul-2005 and not
07-Jan-2005. This is because of the regional settings, I realize. I can't
ask people to change their regional settings, so how can I ensure that if
they enter the date as day-monty-year (numerically) it will actually display
as such? Is this impossible? Is there a macro in existance? I need more
control!

Help!

Have them enter the day, month and year in three separate (and clearly
labelled) cells. You can use the DATE function to calculate the date,
e.g.: =DATE(D2,C2,B2)

SCSC wrote:
I have several workbooks that I distribute where I have formatted the date
columns to display as dd-mmm-yyyy. The users fill in these dates. This is
the way I want them formatted, because that is the way they ultimately need
to be entered into our computer system.

The problem is, if the user looks at the instructions to enter as
dd-mmm-yyyy and mearly thinks, "Ok -- day, monty, year" and enters the 7th of
January, 2005 as 7-1-2005, it will display as 01-Jul-2005 and not
07-Jan-2005. This is because of the regional settings, I realize. I can't
ask people to change their regional settings, so how can I ensure that if
they enter the date as day-monty-year (numerically) it will actually display
as such? Is this impossible? Is there a macro in existance? I need more
control!

Help!



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

That's definitely an option. I need to use the date in a calculation, so
your tip regarding the DATE function is very useful, too!

I'm still hoping there's a way I can do this without having to go into
hundreds of spreadsheets and splitting all the date colums into 3, but at
least this is one option,
if all else fails.

Thanks, Debra.


"Debra Dalgleish" wrote:

Have them enter the day, month and year in three separate (and clearly
labelled) cells. You can use the DATE function to calculate the date,
e.g.: =DATE(D2,C2,B2)

SCSC wrote:
I have several workbooks that I distribute where I have formatted the date
columns to display as dd-mmm-yyyy. The users fill in these dates. This is
the way I want them formatted, because that is the way they ultimately need
to be entered into our computer system.

The problem is, if the user looks at the instructions to enter as
dd-mmm-yyyy and mearly thinks, "Ok -- day, monty, year" and enters the 7th of
January, 2005 as 7-1-2005, it will display as 01-Jul-2005 and not
07-Jan-2005. This is because of the regional settings, I realize. I can't
ask people to change their regional settings, so how can I ensure that if
they enter the date as day-monty-year (numerically) it will actually display
as such? Is this impossible? Is there a macro in existance? I need more
control!

Help!



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html