Understanding a Data Validation formula

[markbt]

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

[#2]

@markbt 

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.

[#3]

@markbt 

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 

[#4 · Accepted solution]

@markbt 

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

This avoids the use of CELL.