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 enter a date in the format Fri 17 Nov, which matches the formatting of the cell, it returns this error
Is some one able to explain what this Data Validation is trying to do please?
many thanks
Another workaround might be to simplify the data validation and use date validation as such:
This avoids the use of CELL.
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
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.
