How can I set a date format for any cell and user has to use that format
otherwise user is not allowed to enter any date.

Hi

you can only do it with code.
The following is some event code which would ensure that only dates can be
entered in column A, strating from row 2.
Adjust to suit your nededs.
also, date format has been set as dd mmm yyyy e.g. 02 Jan 2010, again change
to suit the format you want.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim edate As Date
If Target.Row < 2 Then Exit Sub ' change row number from 2 if required
If Target.Column < 1 Then Exit Sub ' change column number if required
Application.EnableEvents = False
On Error GoTo message
edate = CDate(Target.Value)
Target.Clear
Target.Value = Format(edate, "dd mmm yyyy") ' change format to suit
exitsub:
Application.EnableEvents = True
Exit Sub
message:
MsgBox "You have not entered a valid date"
Target.Clear
Target.Activate
Resume exitsub
End Sub

To use
Copy the code as above
Right click on the sheet tab where you want the validation
View Code
This will open the VB Editor
Paste the code into the white pane that appears
Alt+F11 to return to Excel


--
Regards
Roger Govier

"Kimti" wrote in message
...
How can I set a date format for any cell and user has to use that format
otherwise user is not allowed to enter any date.

__________ Information from ESET Smart Security, version of virus
signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com

It works perfact for one column. Thanks, I have some more columns on same
sheet has some dates and would like to do same thing on them. I was trying to
copy your code and was changing the column, but did not work. I have columns
8, 9, and 10 with the dates in same sheet.

Your help is much appriciated.
Thanks,

"Roger Govier" wrote:

Hi

you can only do it with code.
The following is some event code which would ensure that only dates can be
entered in column A, strating from row 2.
Adjust to suit your nededs.
also, date format has been set as dd mmm yyyy e.g. 02 Jan 2010, again change
to suit the format you want.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim edate As Date
If Target.Row < 2 Then Exit Sub ' change row number from 2 if required
If Target.Column < 1 Then Exit Sub ' change column number if required
Application.EnableEvents = False
On Error GoTo message
edate = CDate(Target.Value)
Target.Clear
Target.Value = Format(edate, "dd mmm yyyy") ' change format to suit
exitsub:
Application.EnableEvents = True
Exit Sub
message:
MsgBox "You have not entered a valid date"
Target.Clear
Target.Activate
Resume exitsub
End Sub

To use
Copy the code as above
Right click on the sheet tab where you want the validation
View Code
This will open the VB Editor
Paste the code into the white pane that appears
Alt+F11 to return to Excel


--
Regards
Roger Govier

"Kimti" wrote in message
...
How can I set a date format for any cell and user has to use that format
otherwise user is not allowed to enter any date.

__________ Information from ESET Smart Security, version of virus
signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Also when I delete the date, it gave me some date in 1900. If I delete the
date I would like to see the cell blank.

"Kimti" wrote:

It works perfact for one column. Thanks, I have some more columns on same
sheet has some dates and would like to do same thing on them. I was trying to
copy your code and was changing the column, but did not work. I have columns
8, 9, and 10 with the dates in same sheet.

Your help is much appriciated.
Thanks,

"Roger Govier" wrote:

Hi

you can only do it with code.
The following is some event code which would ensure that only dates can be
entered in column A, strating from row 2.
Adjust to suit your nededs.
also, date format has been set as dd mmm yyyy e.g. 02 Jan 2010, again change
to suit the format you want.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim edate As Date
If Target.Row < 2 Then Exit Sub ' change row number from 2 if required
If Target.Column < 1 Then Exit Sub ' change column number if required
Application.EnableEvents = False
On Error GoTo message
edate = CDate(Target.Value)
Target.Clear
Target.Value = Format(edate, "dd mmm yyyy") ' change format to suit
exitsub:
Application.EnableEvents = True
Exit Sub
message:
MsgBox "You have not entered a valid date"
Target.Clear
Target.Activate
Resume exitsub
End Sub

To use
Copy the code as above
Right click on the sheet tab where you want the validation
View Code
This will open the VB Editor
Paste the code into the white pane that appears
Alt+F11 to return to Excel


--
Regards
Roger Govier

"Kimti" wrote in message
...
How can I set a date format for any cell and user has to use that format
otherwise user is not allowed to enter any date.

__________ Information from ESET Smart Security, version of virus
signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com