ESFECHA con fórmulas

Creo que el título del post lo dice todo.... vamos a crearnos una fórmula en nuestra hoja de cálculo para determinar si una celda es o no una fecha.
Decir, eso si, que la mejor opción es crear con un poco de programación una UDF:

Function EsFecha(celda As Range) As Boolean
    Application.Volatile
    EsFecha = VBA.IsDate(celda)
End Function

Probablemente de lo más fiable que nos encontraremos...

Sin embargo hoy buscaremos una alternativa (quizá exista alguna más) con el empleo de fórmulas... que pretende acercarse al 99% de efectividad en la detención de fechas.

Como observarás en la columna B hay un gran número de fechas con distintos tipos de formatos, predefinidos y personalizados; y algún otro tipo de dato (texto, números, errores, valores lógicos, ...) con formatos habituales.

Para detectar cuáles son fechas (sin usar macros) es frecuente emplear la función:
CELDA(tipo_de_info;ref)
que entre otros, nos permite recuperar el tipo de formato existente en una celda, de acuerdo (según la documentación de Microsoft) a la siguiente tabla:

Si el formato de Excel es	La función CELDA devuelve
Estándar	"G"
0	"F0"
#.##0	".0"
0	"F2"
#.##0,00	".2"
$#.##0_);($#.##0)	"C0"
$#.##0;(rojo)-$#.##0	"C0-"
$#.##0,00_);($#.##0,00)	"C2"
$#.##0,00;(rojo)-$#.##0,00	"C2-"
0	"P0"
0	"P2"
0	"C2"
# ?/? o # ??/??	"G"
d/m/aa o d/m/aa h:mm o dd/mm/aa	"D4"
d-mmm-aa o dd-mm-aa	"D1"
d-mmm	"D2"
mmm-aa	"D3"
mm/dd	"D5"
h:mm a.m./p.m.	"D7"
h:mm:ss a.m./p.m.	"D6"
h:mm	"D9"
h:mm:ss	"D8"

Que es la fórmula aplicada en la columna C de la imagen...:
CELDA("formato";B2)
Observarás como muchos de las celdas con fechas, y formatos no definidos en la tabla anterior, no los identifica con códigos de tipo fecha: D1,D2,D3,D4,D5...
Una pena :-(
Aunque sería válida, y fácil de usar, en muchas ocasiones...
Buscamos en todo caso algo más de precisión.

Así pués, después de darle vueltas al asunto pensé que una solución más fiable podría estar en el formato aplicado en la celda..
Y una forma de recuperar el formato de una celda sin emplear programación en VBA es el uso de las macrofunciones 4.0 de Excel, sí, esos dinosaurios aún están disponibles.
En concreto usaremos INDICAR.CELDA (GET.CELL en inglés) (ver ejemplos aquí).
En esta ocasión usaremos el parámetro 7 que nos devuelve precisamente el formato de una celda.
RECUERDA que esta función debes escribirla en un contexto de nombre definido!!
Para ello sitúate en alguna celda de la fila 2 (D2) y añade el nombre definido siguiente:

Fíjate en la fórmula añadida:
=INDICAR.CELDA(7;'Hoja1 (2)'!$B2)&SI((AHORA()*0)=0;"")
donde hemos añadido una parte 'volatil' con la función AHORA().

Si aplicamos esta fórmula en la columna D:
=FormatoCelda

En este caso, no sería necesario hacer visible en la hoja de cálculo esta fórmula, ya que la incorporaremos en nuestra fórmula final.

¿Cuál es la línea de trabajo entonces?... fijarnos en el formato de celda, sea el que sea, y buscar alguno de los tres caracteres básicos de un formato de fecha: d m a
OJO con los formatos personalizados que pueden incluir colores entre corchetes, o textos añadidos como prefijos y sufijos entre comillas...igualmente existen dos formatos genéricos: 'General' o 'Estándar' (según versión de Office).
Por supuesto es imposible cubrir el 100% de formatos personalizados aplicables, pero al menos discriminaremos los máximos posibles: quitar colores de fuente y textos entrecomillados.

Para eliminar estas 'subcadenas de texto' del formato de celda obtenido con INDICAR.CELDA usaremos las función SUSTITUIR aplicada sobre la división de ésta en partes (lo que lograremos con XMLFILTRO) Con todos estos antecedentes insertaremos nuestra fórmula, apoyándonos en LET para facilitar su comprensión y construcción. En la celda E2 añadimos:
=LET(formato;FormatoCelda;
EliminoCaracteresNoDeseados;SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(formato;"""";"[");">";"&gt;");"<";"&lt;");"]";"[");
formatoLimpio;UNIRCADENAS("";1;SI.ERROR(INDICE(SI.ERROR(XMLFILTRO("<i><f><d>"&SUSTITUIR(EliminoCaracteresNoDeseados;"[";"</d><d>")&"</d></f></i>";"//f/d");"");SECUENCIA(LARGO(formato);1;1;2));""));
TipoFecha;SUMA(SI.ERROR(ENCONTRAR({"d"\"m"\"a"};formatoLimpio);0));
SI(O(formatoLimpio="Estándar";formatoLimpio="General");"No fecha";SI(TipoFecha>0;"Es fecha";"No fecha")))

En la primera línea de nuestra fórmula obtenemos el formato de la celda a estudio, llamando a la macrofunción generada previamente 'FormatoCelda':
=LET(formato;FormatoCelda;

En la siguiente sustituimos caracteres ASCII que pueden dar problemas a la función XMLFILTRO, tales como: < > "comillas" o el & Los sustiutiremos por sus equivalentes en HTML:
> por &gt; o &#62;
< por &lt; o &#60;
& por &amp; o &#38;
o
" por &quot; o &#34;
entre otros...
De ahí la siguiente línea en nuestra fórmula:
EliminoCaracteresNoDeseados;SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(formato;"""";"[");">";"&gt;");"<";"&lt;");"]";"[");
donde sustituimos caracteres especiales, y finalmente el cierre de corchete lo cambiamos por 'apertura corchete' .... de tal forma que solo tengamos ese caracter [ para determinar las partes/subcadenas a 'eliminar.

El paso siguiente divide con XMLFILTRO en secciones nuestro formato:
formatoLimpio;UNIRCADENAS("";1;SI.ERROR(INDICE(SI.ERROR(XMLFILTRO("<i><f><d>"&SUSTITUIR(EliminoCaracteresNoDeseados;"[";"</d><d>")&"</d></f></i>";"//f/d");"");SECUENCIA(LARGO(formato);1;1;2));""));
UNIRCADENAS se encarga de volver a unir solo las partes que nos interesan, dejando fuera/eliminando de la nueva composición, aquello que inicialmente estuviera entre corchetes [...] o entre comillas "...".
Lo que conseguimos recuperando las filas indicadas con el vector:
SECUENCIA(LARGO(formato);1;1;2)
Serían, curiosamente solo las filas impares!!

Por tanto nos quedamos exclusivamente con la 'parte pura del formato'

En el paso siguiente buscamos los caracteres d, m y a típicos de las fechas:
TipoFecha;SUMA(SI.ERROR(ENCONTRAR({"d"\"m"\"a"};formatoLimpio);0));
si encuentra alguno de esos caracteres sumará, y por tanto deberemos suponer que el formato corresponde a una fecha.

Finalmente, con todos los cálculos aplicados, nuestra fórmula termina con un condicional que discrimina dos formatos: General o Estándar, que por contener alguno de esos caracteres: m d a nos confundirían:
SI(O(formatoLimpio="Estándar";formatoLimpio="General");"No fecha";SI(TipoFecha>0;"Es fecha";"No fecha")))
Si nos fijamos en los formatos mas habituales, nuestra fórmula funciona perfectamente... e incluso para fechas con formatos personalizados:

Es curioso el último formato del ejemplo:
[Azul][<10]0,0;[Verde][>200]d/m/aa;[Amarillo]"dma";@*.
donde la función de VBA ISDATE no lo identifica como fecha, y como nuestra fórmula, al encontrar esa parte d/m/aa de la segunda sección del formato personalizado, sí retorna un verdadero, reconociéndola como fecha...

Insisto que no cumple el 100% de los casos, pero está muy cerca ;-)