SOLVED

# Understanding a Data Validation formula

Copper Contributor

# 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

3 Replies

# Re: Understanding a Data Validation formula

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.

# Re: Understanding a Data Validation formula

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

best response confirmed by markbt (Copper Contributor)
Solution

# Re: Understanding a Data Validation formula

Another workaround might be to simplify the data validation and use date validation as such:

This avoids the use of CELL.

1 best response

Accepted Solutions
best response confirmed by markbt (Copper Contributor)
Solution

# Re: Understanding a Data Validation formula

Another workaround might be to simplify the data validation and use date validation as such:

This avoids the use of CELL.