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.

  • 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 dd.mm.yy. 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...

  • 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

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.