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.
One of these must be true:
NOT(ISERROR(DATEVALUE(I8)))
The result of converting a text string that looks like a date to a date is not an error (It's a date)
AND(ISNUMBER(I8), LEFT(CELL("format", I8)) = "D").
I8 is a number and it's in a date format (Date format uses "D1", "D2", "D3", etc.). LEFT is being used to determine if the result of CELL contains a "D" prefix.
Your entry is not accepted because it's text and DATEVALUE is not able to convert it to a date. You could format the cell as ddd mmm d and enter the date as 11/17 as a workaround.