Possible to Enforce Users to place dates in a column using dd/mmm/yyformat

[Mike C]

Hello - is it possible to use validation to ensure that users (a) use
the date format in a given column, and do not stray from the dd/mm/yy
format.

Is formating the column with a custom format my only option? Can I
also lock the format for a particular column in some way?

Thanks in advance for any suggestions.

 

[Tyro]

Date formatting is not for data entry. Dates are numbers. Formatting is used
to express the numbers for visualization, not data entry. Formatting does
not make people enter Excel date numbers which range from 1 to 2,958,465
When those numbers are formatted for display purposes as dates, they display
as Jan. 1. 1900 and Dec. 31, 9999 March 22, 2008 is day 39,529. If you
want to restrict entry in a cell of a date in a certain format, it requires
Visual Basic code to accept only the date in that format. Nonetheless, the
date to be an Excel date will still be a number.

Tyro

 

[Dave Peterson]

I would think it would be a good idea to let the users enter the date anyway
they want. But then let the format of the cell display it the way that you
want.

You can unlock the range (and any other cells that require input from the user)
via:

Format|Cells|Protection tab|Check locked
(xl2003 menus)

Then protect the worksheet
Tools|Protection|Protect sheet

This will allow the user to change the value, but not the format.

But be aware that there are lots of things that are disabled when you protect a
sheet. You may want to test a bit to see the worksheet is still functional for
you when it's protected.

And worksheet protection is easily broken. You may find a dedicated user who
still screws up your formatting.

ps. Data|Validation is easily overcome, too. Copy|Pasting over the cell with
Data|Validation can break it.