Forum Discussion

arthur1030's avatar
arthur1030
Copper Contributor
Feb 05, 2024

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?

  • 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.

Resources