lunes, 28 de julio de 2014

Validando horas y fechas en Excel usando fórmulas

Supongamos que tenemos una base de datos con varios registros y una de sus columnas contiene un campo compuesto por fecha y hora, como por ejemplo: 27/07/2014  09:39:01 a.m. Ahora supongamos que necesitamos validar si en esa columna se cumplen ciertas condiciones como por ejemplo una determinada fecha y hora, o un determinado mes y día, etc. Lo primero que se podría hacer es aplicar filtros a la columna y con esto obtendríamos los resultados que necesitamos.

¿Pero qué pasa si la base de datos contiene muchos registros? El uso de filtros que de por si es una tarea muy manual podría ser un poco tedioso, ya que si se tienen registros de diferentes fechas la cantidad de opciones a seleccionar y descartar son muchas, y si por algún motivo hay registros con la fecha en otros formatos el uso del filtro se descontrola más. El siguiente ejemplo ilustra una base de datos con tres columnas que contienen el ID del registro, la fecha de registro y el sexo; la base de datos contiene más de 50.000 registros y algunos registros tienen el formato incorrecto:

Ejemplo de Base de datos en Excel
Ejemplo de Base de datos en Excel

Supongamos que necesitamos obtener los registros del día 17 de abril del 2014 sin importar la hora, si lo hacemos de forma manual con filtros la cantidad de clics que se tendría que hacer es un poco extensa. Una solución viable es aplicar una formular que valide el contenido de las celdas de la columna FECHA_REGISTRO y nos diga si cumple o no cumple con la condición (que el registro sea del 17 de abril del 2014).

Pensando de forma algorítmica una solución lo primero que se debe validar es que en el registro el año sea igual a 2014, luego que el mes sea igual a abril y finalmente que el día sea 17; si se cumplen las tres condiciones se debe marcar el registro como CUMPLE, en caso contrario como que NO CUMPLE.

Para lograrlo primero hacemos una conjunción de las tres condiciones usando la función Y() que funcionancomo el operador and de la mayoría de los lenguajes de programación; es decir, retorna verdadero si todas las condiciones que se le pasen como parámetros se cumplen. Para evaluar el día, mes y año utilizamos las funciones que nos provee Excel y que tienen su mismo nombre:

=Y(AÑO(B2)=2014;MES(B2)=4;DIA(B2)=17)

Y finalmente valido si la condición se cumple con la función SI(), que valida si una expresión es verdadera o falsa (para nuestro caso las tres condiciones propuestas) y realiza una acción si se cumple y otra si no; para ello anido las formulas así:

=SI(Y(AÑO(B2)=2014;MES(B2)=4;DIA(B2)=17);"CUMPLE";"NO CUMPLE")

La anterior fórmula la ponemos en una nueva columna y ya solo nos resta hacer un filtro y seleccionar los registros que cumplen o no con la condición que estamos evaluando:


Aplicando filtros en Excel
Aplicando filtros en Excel

Como vemos es posible sacarle mucho provecho a Excel aplicando lógica de programación y haciendo uso de sus formulas, sin necesidad de programar macros que tienen un nivel de complejidad un poco más alto para los no expertos en la materia.