Data validation - dates

[Slot]

Hi

I want the users of my spreadsheet to insert a date, formatted as
dd-mm-yy (06-09-07).

I don't want any other formats, e.g. ddmmyy, ddddmmmmyyyy etc.

How do i use data validation to ensure this?

 

[Guest]

It is probably simpler to just format the cells to the way you want them to
look and the user then enters the date in the formula bar as per the short
date format. (Usually 6/9/07). The system then reformats it to the format
chosen by you.

I am assuming that you are new to this type of thing so the following are
some instructions to get you started.

To Format cells in xl 2007:-

Select the cells to be formatted.
On the Home ribbon click the little arrow bottom right of the Number block
(Centre of Home ribbon). Then go to instructions under all versions below.

To Format cells in earlier versions of xl:-

Select the cells to be formatted.
Click on menu item Format->Cells.

Continue here for all versions:-
Then select the Number tab then Date->Custom and then in the box just under
'Type:' Edit the format to dd-mm-yy.

Even if you use complex data validation, you will still need to format the
cells as above to display the dates in your preferred format otherwise they
will display in the short date format for your operating system.

Tip 1: Dates cannot be entered as ddmmyy without the delimiters between day
and month and month and year. They must be entered with delimiters such as
dd/mm/yy.

Tip 2: Custom format ddd dd mmm yyyy will display the alpha abbrev week day
at the beginning and the alpha abbrev month like this:-
Thu 06 Sep 2007.

Tip 3: Custom format dddd dd mmmm yyyy will display the full alpha week day
at the beginning and the full alpha of the month like this:-
Thursday 06 September 2007.

Hope this helps.


Regards,

OssieMac