change date format in excel data validation

3
votes

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.

3
excelexcel-formula
Good question, with good answers, but not programming related. Should be migrated to Superuser - chris neilsen

3 Answers

5
votes

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

dates

4
votes

I come across these type of questions often at work, although not yet that exact one you have asked. My firsrt inclination is to check regional settings on your computer. Believe it or not, this area controls what Excel deems as a decimal point and things of that nature. There is an area under Regional settings that specifies your date format. Your specific format of 'dd-mm-yyy' is listed there. I would start with that.

0
votes

When applying the validation use the Date option and apply Start & End dates accordingly.

Format the cell to use dd/mm/yyyy format - once valid date is entered it will be formatted as required regardless of how it was input (eg 1/2/9 will display correctly as 01/02/2009)