Data validation for multiple date formats

Copper Contributor

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:
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?

1 Reply


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.