Verificar la edad sin morir en el intento en Google Sheets (y otras cosas)


Hace unos días me escribía un conocido de mi tuitesfera preguntando por el modo de utilizar las reglas de formato condicional para señalizar visualmente si la fecha de nacimiento registrada en cualquier celda de una hoja de cálculo corresponde a alguien de al menos 14 años o no (ponga usted la edad que desee). Esto es algo bastante útil cuando se gestionan, por ejemplo, inscripciones a un evento restringido por edad. Aunque el problema es relativamente sencillo, voy a desarrollarlo con cierto detenimiento aunque, espero, manteniendo la brevedad. Es posible que lo que siga te interese si no te sientes muy seguro usando fórmulas de formato condicional y las expresiones lógicas que suelen verse en ellas. Además, hay un detalle relativo a la aritmética de fechas que quizás no sea demasiado evidente. Adelanto, sin hacer spoilers, que el artículo tiene un giro final tal vez inesperado.

Vamos con ello. 

La cosa parece de entrada bastante obvia. Se trata de tirar de la posibilidad de introducir una fórmula personalizada que calcule el tiempo que ha pasado desde el valor de fecha indicado en una celda hasta el momento actual. Si ese valor supera un determinado umbral  (el individuo tiene tantos o más años) la fórmula devolverá un valor verdadero y se aplicará el formato indicado, en caso contrario se evaluará a falso y la regla de formato no se activará.

Por un lado, para obtener la fecha actual podemos recurrir a la función HOY(). Por otro, cuando se restan dos valores de tipo fecha, el resultado es el número de días trascurridos entre ambas.

De hecho, los valores de fecha se almacenan internamente como números enteros a partir del 31/12/1899. No tienes más que hacer Formato  → Numero → Numero sobre una celda que contenga una fecha para revelar su auténtica naturaleza numérica.

Así que blanco y en botella. En principio podría parecer suficiente utilizar una expresión como esta en el campo Valor o fórmula del panel de formato condicional ¿verdad?

= HOY() - C2 >= 14 * 365

No, los paréntesis no son necesarios dado que el orden de precedencia de operadores (resta, comparación y multiplicación) no requiere en este caso de agrupación alguna que altere su prioridad natural de aplicación. Si tu TOC te puede, no obstante, no va a pasar nada porque los añadas generosamente. Yo pasaré de ellos porque mi terapeuta me los tiene prohibidos (cuando no son necesarios).

= (HOY() - C2) >= (14 * 365)

Aunque no es estrictamente necesario, quizás te resulte más intuitivo reescribir la expresión anterior en forma de IF(). Recuerda que en ese caso debes asegurarte de que la función devuelva exclusivamente valores VERDADERO (se aplicará el formato) o FALSO (no se aplicará el formato), que es lo que espera la regla condicional.

= SI(HOY() - C2 >= 14 * 365 ; VERDADERO ; FALSO)

Montemos pues una pequeña hoja de cálculo para comprobar si realmente esto nos solventa la papeleta o no.


Vaya, parece que funciona. Como esas celdas vacías coloreadas no quedan nada bien, mejoremos un poquito nuestra fórmula personalizada:

=Y(NO(ESBLANCO(C2)) ; HOY() - C2 >= 14 * 365)


Estupendo. Hemos utilizado las funciones lógicas Y() y NO(), además de ESBLANCO(), para construir una expresión lógica que solo se evalúa a cierto con celdas NO vacías Y en las que además la fecha de nacimiento representada cumple la condición aritmética expresada en la fórmula. Para evitar el uso de la función NO() podríamos haber utilizado ISDATE() en lugar de ESBLANCO(), pero bueno, no tiene demasiada importancia, y así además vemos mundo formulaico.

Pero ¿seguro que nuestra fórmula valida correctamente la edad? Veámoslo.


¡Demonios! Pues resulta que no lo hace como debería. Si se indica como fecha de nacimiento el 24, 25 o 26 de mayo de 2005 (recuerda, hoy es 23/05/19 y se exige tener 14 años o más) nuestra tontifórmula de formato condicional da por buena la edad. De hecho, hasta el 27 de mayo no se entera de que no se cumple la condición que le exigimos a la fecha de nacimiento.

¿Qué ha pasado? Pues ha pasado que la misma fecha en dos años consecutivos no siempre está separada por 365 días. Y la culpa la tiene esa anormalidad denominada año bisiesto necesaria para corregir las discrepancias entre la duración del año trópico y el año - calendario. Por esa razón hay años con un día de más. Estos días se van acumulando, distorsionando el cálculo. En el ejemplo anterior, entre el 2005 y el 2019 hay 3 años bisiestos (+3 días). Por esa razón los nacidos el 24, 25 y 26 de mayo pasan el corte de edad indebidamente.

La realidad analógica es infinitamente compleja, al parecer.

¿Y qué hacemos ahora? Pues complicar las cosas, claro. Veamos, podemos cotejar secuencialmente y siguiendo una lógica de control apropiada años, meses y días de la fecha de nacimiento y de la actual. Una posible secuencia de acciones es la representada en este diagrama de flujo.

Comprobando la edad, pasito a pasito.

Básicamente se trata de comparar, tras verificar que la celda no está vacía, el número de años que han transcurrido desde la fecha de nacimiento hasta la actual. Si se supera el valor umbral (14 en este caso) tenemos la seguridad de que la condición se satisface, con independencia de día y mes en que se cumplan años (andamos al menos ya por los 15). Si por el contrario esa diferencia se sitúa justo en el límite pasamos a valorar los meses. La tercera posibilidad es que la diferencia de años sea inferior a nuestro valor umbral, en cuyo caso terminamos inmediatamente sin aplicarle formato a la celda ni realizar más comprobaciones.

Si hemos llegado hasta aquí es porque nuestro individuo cumple el número de años considerado límite a lo largo del año natural actual. Por tanto, si ahora resulta que el mes en el que nos encontramos es posterior al de la fecha de nacimiento, sabemos con certeza que nuevamente se cumple la condición, sin necesidad de tener en cuenta los días del mes, y por tanto hay que colorear la celda. En caso contrario, si los meses son coincidentes, hay que comparar los días. Nuevamente tenemos como  tercera posibilidad que el mes actual sea anterior al de la fecha de nacimiento, lo que supone simplemente terminar sin aplicar formato, del mismo modo que ocurría en la evaluación inicial de años.

Por último, por tanto, solo resta cotejar los días de ambos valores de fecha, de manera natural y consecuente.

Y ya está.

Ahora solo nos queda traducir este diagrama a una expresión que devuelva un valor VERDADERO o FALSO, requerido por la regla de formato condicional cuando se usa una fórmula personalizada.  Para descomponer un valor de fecha en años, meses y días se utilizan ¡oh sorpresa! las funciones AÑO(), MES() y DIA(). Combinando y asociando sabiamente los operadores Y y O podemos llegar a esto:

Y(NO(ESBLANCO(D2));O((AÑO(HOY())-AÑO(D2))>14;Y((AÑO(HOY())-AÑO(D2))=14;O(MES(HOY())>MES(D2);Y(MES(HOY())=MES(D2);DIA(HOY())>=DIA(D2))))))

Si te parece una marcianada, tal vez quieras reescribir la fórmula utilizando varias funciones SI() anidadas, tal y como te sugería unos párrafos más arriba. Recuerda, la fórmula debe devolver VERDADERO o FALSO.



Ahora sí.

No es la fórmula más comprensible del mundo. Pero es la nuestra. Y la queremos. Si la desenrollas con cuidado ¡ojo con los paréntesis! mientras no le quitas ojo al diagrama de flujo anterior seguro que no te resulta difícil comprenderla.

No puedo negar que nos encontramos aquí con una de esas situaciones en las que se produce una pequeña explosión de complejidad al utilizar fórmulas en una hoja de cálculo. Los que asististeis a mi taller en GEG Valencia 2019 seguro que recordáis a qué me refiero.

¿Y ya está? Pues no. Vamos con el truco final.

Resulta que hemos sido unos pardillos. De libro. Ya existe una función integrada en el repertorio disponible en las hojas de cálculo de Google que tiene en cuenta todas estas historias de años bisiestos y tal y permite calcular el número de años, meses y días ¡y más! entre dos valores de fecha dados. Su nombre es SIFECHA(). Como anécdota, decir que su denominación en castellano parece una pésima traducción del nombre original en inglés, DATEDIF(). Al parecer, alguien en Google con dos carajillos de más se ha comido la segunda "D" y ha colocado delante el "IF" transformado en "SI". Maravilloso. El caso es que un vistazo poco atento al catálogo de funciones de fecha puede ocultar su verdadera utilidad, llevándonos tal vez a pensar que su propósito es indicar si el contenido de una celda es un valor de fecha o no... cosa que ya hace ISDATE(), por otra parte.


Así que podemos fácilmente implosionar nuestra explosiva complejidad y dejar la fórmula personalizada que activa la regla de formato condicional en algo tan simple y hermosote como esto:

=Y(NO(ESBLANCO(E2)) ; SIFECHA(E2 ; HOY() ; "Y") >= 14)

Y, lógicamente, funciona de perlas (en acción en la columna regla condicional 3):


Ahora sí. Fin de la historia. ¿Sacamos algo de todo esto? Sí, por favor:

Conclusión:
Verifica tus modelos de cálculo con datos de entrada que los pongan a prueba exhaustivamente en todos los casos posibles. 
Corolario 1:
Asegúrate de conocer la herramienta que estás utilizando. Probablemente te ahorrará tiempo y te permitirá reducir la complejidad.
Corolario 2: 
El diablo está en los detalles.
(¡Vaya si lo está!)

¿Comentarios? La cajita de aquí abajo es toda tuya.

Comentarios