Hi Norman,
I would have agreed with you until recently.
I automated a worksheet for import shipping details.
The sheet is redrawn from scratch each time and all formatting cleared prior
to redraw.
All constants are automated and all variables are user entered via input
boxes to minimize error.
All is perfect..., except that the dates are erratic.
I live in Australia so the format is "dd-mm-yyyy" the problem is that no
matter how I enter the dates Excel reformats to "mm-dd-yyyy"
...no big deal you say???
It only does this for the 1st 12 days for the month. Then the date format
works fine...as directed by the format.
I've exhausted all brain cells & it's still driving me crazy.
Yes I've even resorted to the macro recorder to try & resolve the issue..
still no good.
Work that one out....!

"Norman Jones" wrote in message
...
Hi Dev,

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


---
Regards,
Norman


"Dev" wrote in message
...
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

On Sun, 20 May 2007 02:18:13 GMT, "DAMAN" wrote:

Hi Norman,
I would have agreed with you until recently.
I automated a worksheet for import shipping details.
The sheet is redrawn from scratch each time and all formatting cleared prior
to redraw.
All constants are automated and all variables are user entered via input
boxes to minimize error.
All is perfect..., except that the dates are erratic.
I live in Australia so the format is "dd-mm-yyyy" the problem is that no
matter how I enter the dates Excel reformats to "mm-dd-yyyy"
..no big deal you say???
It only does this for the 1st 12 days for the month. Then the date format
works fine...as directed by the format.
I've exhausted all brain cells & it's still driving me crazy.
Yes I've even resorted to the macro recorder to try & resolve the issue..
still no good.
Work that one out....!


That behavior is typically due to your Windows Regional Settings (Start/Control
Panel/ ... then it depends on your version of Windows and CP display how you
get to it) being set to mm-dd-yy or something similar.

If you examine your entries carefully, you will likely see that the dates after
the 12th of the month are TEXT and not true DATES (or numbers).

Change your Windows Regional settings to Australian, and you should be OK.
Make sure the date format is DMY.
--ron