Data validation for multiple date formats

Copper Contributor

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?

1 Reply

@arthur1030 

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.