Forum Discussion
Data validation for multiple date formats
Hi,
I want to use Data Validation to allow the user to enter the date in a column. However, I would like them to be able to enter the date in any of the following formats:
YYYY/MM/DD
DD/MM/YYYY
DD/MM/YY
I have tried creating a custom validation criteria but I do not seem to get it to work. I couldn't even get the formula *ISNUMBER(SEARCH("[a-z,A-Z]"; F7))=False* to work.
Anyone have any suggestions?
Excel checks the operating system settings to determine whether an entry is a valid date. So if the system date setting is yyyy/mm/dd, then dd/mm/yyyy will not be recognized as a date, regardless of data validation rules.
A format that will always be recognized is d-mmm-yy or d-mmm-yyyy, for example 5-Feb-2024.
