Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to put together a 'Data Validation' formula which restricts the user to entering a date in the following format: dd-mm-yy.

From the research I've done, I've come across what I thought would be a solution here:

Format restrictions for cell in Excel

But unfortunately I'm unable to get this to work.

Entering the date 01-01-13 which should, if I've understood it correctly pass the validation, actually fails and I receive the validation error message.

I just wondered whether someone may be able to look at this please and offer some guidance on where I may have gone wrong.

share|improve this question
Did you try using the Data tab / Data Validation to allow only dates? Perhaps that'll work better than a formula. – Karan Feb 2 '13 at 17:23
Hi @Karan, thank you for taking the time to reply to my post. I think I'll go with this option. I've tried many different formulas I've found, with limited success, this, at least I know works. Thank you for your time and regards. Chris – IRHM Feb 2 '13 at 18:23

Let me just add my 2c to your question/answer:

Trying to force the user to enter a date in exactly the format you want is usually less effective then using data validation in combination with the formatting of the cell: The data validation will for instance also allow dates entered as dd-mm-yyyy - but if you set the format of the cell to 'dd-mm-yy' will convert the input to the format you want - or give an error message if the date entered is not a proper date.

Even better, this will also work, if you deploy your worksheet on a system with another date format than yours, say Excel will then still recognize the date entered by the user in his format. And if you insist, you can still output the date in your country specific way (by providing the right local in the format number dialog).

Internally, Excel will handle all the dates equally anyway, no matter how there are entered or displayed, so your calculations will work if you use the data validation...

share|improve this answer
Hi @Peter Albert, thank you very much for taking the time to offer your guidance. It's incredible really how many different ways there are to approach these problems. Once again many thanks and kind regards. Chris – IRHM Feb 5 '13 at 17:09
up vote 1 down vote accepted

From further research and with the guidance provided by @Karan, I've gone for the built in Data Validation option.

share|improve this answer

You must log in to answer this question.

protected by JakeGould Jan 8 at 2:21

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged .