Can we define a date format in a cell

[Guest]

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

 

[Norman Jones]

Hi Dev,

If you pre-format the cell with the required date format,
any date subsequently inserted will be displayed in the
requisite format.

 

[Guest]

Take a look at Data Validation and see if that gets you what you want.

HTH,
Barb Reinhardt

 

[Guest]

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.

 

[Guest]

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

 

[Norman Jones]

Hi Dev,

Perhaps, consider using a calendar control to enable
your users to enter the date information.

See Ron de Bruin at:

Use the Calendar control to fill in dates
http://www.rondebruin.nl/calendar.htm

 

[Guest]

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.