there is no built in support for it.

As a test,
I tried data validation to force the user to enter Jan . . . using a
formula like

=UPPER(LEFT(G5,3))="JAN"

and it didn't work. I could just enter the letters Jan. This would
indicate to me that data validation is looking at the underlying date serial
number. the change event wouldn't kick in until after the entry is complete.
So I don't see it as an option, but maybe someone else has a creative idea.

--
Regards,
Tom Ogilvy




"Dev" wrote:

Hi all,

Thank you so much for all your suggestions. The reason I am asking for
defining a date format is primarily to compel the enterer to enter date in a
specific format to avoid any errors. Normally I notice that different people
updating a spreadsheet generally enter dates in different formats which
causes a lot of issues while doing ageing analysis involving rework and
reformatting to get it all in one standard. Hence if we can have a format
predefined when the person is entering the date it will not allow him to make
an error.

I hope I have been clear in explaining.

Thanks again

Dev

"Tom Ogilvy" wrote:

Just to add to Norm's excellent advice:
Dates are stored as the number of days since a base date. You format the
cell to have it appear as you like. You can see this by entering the same
date into a variety of adjacent cells. Then select those cells and format
them as General and you will see they all hold the same value. Now format
them as you want them to appear.

--
Regards,
Tom Ogilvy



"Dev" wrote:

Hi there,

Can we define a date format in a cell to restrict only dates with certain
formats can be entered.

Thanks for your help in advance.

Dev