lunes, 17 de noviembre de 2014

Especificar Tipo de Formato de una Celda

"Regularmente me envían un listado con diferentes entradas en una columna y necesito detectar, por medio de fórmulas, cuáles de dichas entradas son fechas".

Para solucionar este problema haremos uso de la función CELDA. Partimos del siguiente ejemplo:
La función CELDA devuelve información acerca del formato, la ubicación o el contenido de una celda. La sintaxis de esta función es CELDA( tipo_de_info;referencia), tal y como muestra la ayuda de excel, y tiene los siguientes argumentos:

tipo_de_info: Es un valor de texto que especifica el tipo de información de la celda que se desea obtener. La siguiente lista muestra los posibles valores del argumento de tipo_de_info y los correspondientes resultados:

tipo_de_infoDevuelve
"DIRECCION"la referencia, en forma de texto, de la primera celda del argumento ref.
"COLUMNA"El número de columna de la celda del argumento ref.
"COLOR"

Valor 1 si la celda tiene formato de color para los valores negativos; de lo contrario, devuelve 0 (cero).

"CONTENIDO"Valor de la celda superior izquierda de la referencia, no una fórmula.
"ARCHIVO"

Nombre del archivo (incluida la ruta de acceso completa) que contiene la referencia, en forma de texto. Devuelve texto vacío ("") si todavía no se ha guardado la hoja de cálculo que contiene la referencia.

"FORMATO"
Un valor de texto correspondiente al formato numérico de la celda. Los valores de texto para los distintos formatos se muestran en la siguiente tabla. Si la celda tiene formato de color para los números negativos, devuelve "-" al final del valor de texto. Si la celda está definida para mostrar todos los valores o los valores positivos entre paréntesis, devuelve "()" al final del valor de texto.

"PARENTESIS"

Valor 1 si la celda tiene formato con paréntesis para los valores positivos o para todos los valores; de lo contrario, devuelve 0 (cero).

"PREFIJO"
Un valor de texto que corresponde al "prefijo de rótulo" de la celda. Devuelve un apóstrofo (') si la celda contiene texto alineado a la izquierda, comillas (") si la celda contiene texto alineado a la derecha, un acento circunflejo (^) si el texto de la celda está centrado, una barra inversa (\) si la celda contiene texto con alineación de relleno y devolverá texto vacío ("") si la celda contiene otro valor.

"PROTEGER"
Valor 0 (cero) si la celda no está bloqueada; de lo contrario, devuelve 1 si la celda está bloqueada.

"FILA"
El número de fila de la celda del argumento ref.

"TIPO"
Un valor de texto que corresponde al tipo de datos de la celda. Devolverá "b" (para blanco) si la celda está vacía, "r" (para rótulo) si la celda contiene una constante de texto y "v" (para valor) si la celda contiene otro valor.

"ANCHO"El ancho de columna de la celda redondeado a un entero. Cada unidad del ancho de columna es igual al ancho de un carácter en el tamaño de fuente predeterminado.

referencia: (argumento opcional) La celda sobre la que desea información. Si se omite, se devuelve la información especificada en el argumento tipo_de_info para la última celda cambiada. Si el argumento de referencia es un rango de celdas, la función CELDA devuelve la información sólo para la celda superior izquierda del rango.

He destacado en naranja "formato" porque es el tipo_de_info con el que vamos a trabajar. Para ello nos situamos por ejemplo en la celda E3 y escribimos la fórmula:

=CELDA("formato";B3)   y copiamos hasta E9:
Como se puede ver, en aquellas celdas que tenemos formato de fecha obtenemos la referencia D1. Haciendo uso de la ayuda de Excel, la siguiente lista describe los valores de texto que devuelve la función CELDA cuando el argumento tipo_de_info es "formato":

Si el formato de Excel es
La función CELDA devuelve
Estándar
"G"
0
"F0"
#.##0
".0"
0,00
"F2"
#.##0,00
".2"
$#,##0_);($#,##0)
"C0"
$#.##0;(rojo)-$#.##0
"-M0"
$#.##0,00_);($#.##0,00)
"C2"
$#.##0,00;(rojo)-$#.##0,00
"-M2"
0%
"P0"
0,00%
"P2"
0,00E+00
"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"

Como se puede comprobar, todos los formatos de fecha comienzan por la letra D. Por ello hacemos ahora la siguiente fórmula en la celda D3:
=SI(IZQUIERDA(E3;1)="D";"Sí";"No")   y copiamos hasta la celda D9:
Evidentemente, podríamos resolver el modelo con una única fórmula en D3 que nos evitaría la columna E, a saber:
=SI(IZQUIERDA(CELDA("formato";B3);1)="D";"Sí";"No")

IMPORTANTE: Si el argumento tipo_de_info de la función CELDA es "formato", como en nuestro caso, y procedemos a asignar un formato diferente al inicial a la celda a la que se hace referencia, es necesario volver a calcular la hoja de cálculo (o pulsar F9) para poder actualizar los resultados de dicha función.

Si queremos destacar en otro color aquellas entradas que son fechas entonces tenemos que hacer uso de la herramienta de Formato condicional. Para ello seleccionamos el rango B3:B9 y vamos a Formato condicional y formulamos como se detalla en la imagen a continuación: