Forum Discussion
Solved
Understanding a Data Validation formula
One of our employees has imported a Google Sheet into Excel and one of the cells contains this formula; =OR(NOT(ISERROR(DATEVALUE(I8))), AND(ISNUMBER(I8), LEFT(CELL("format", I8))="D")) If you ente...
Another workaround might be to simplify the data validation and use date validation as such:
This avoids the use of CELL.
The formula tries to check whether the value entered is either a date, formatted in a number of special ways, or a text value that looks like a valid date.
The problem is that the part CELL("format",I8) is not reliable - it returns "G" instead of "D..." for many date formats in Excel.
See the section Cell Format Codes in CELL function