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.
Best Answer
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...