change date format in excel data validation

Question

I have an Excel Column which has number format as dd-mm-yyyy. Formatting of values are working fine.

Now, I need to add StartDate limit to that particular column, using Date criteria in Data Validation.

When I tried to set StartDate as '15-12-2001', I'm getting an error message: "the start date you entered is invalid". But, if I enter StartDate as '12-15-2011' (ie, of format: 'mm-dd-yyyy'), its working fine.

How can I changes the date format in data validation to accept StartDate in 'dd-mm-yyyy' format?

Any help will be appreciated.

Note: This is not a programming question.

Answer 1

You most probably have your computers regional settings (accesible via control panel) set to a US type (mm-dd-yyyy). So you can either

• Change the regional setting via control panel
• Use the DATE function instead (which I would recommend given there is likely to be a good reason for your regional settings being a certain way)

The picture below

1. Has my machine set to US settings
2. has column A formmatted as dd-mm-yyyy
3. has used data validation using the DATE function to set a range of 15-Dec-2001 to 20-Dec-2001

If you look at the formula bar you can seen I have entered 12/17/2001 in mm-dd-yyyy format (ie 17th Dec 2001) and the validation has accepted it, then the cell is reformated to 17-12-2001 by the dd-mm-yyyy format