¿De quién es el próximo cumpleaños?

Ya he comentado que cálculos de fechas y horas es uno de los temas más consultados por los lectores de este blog. No es casualidad. La forma en que Excel trata las fechas y las horas no es intuitiva.
Un lector me pedía ayuda con un modelo para encontrar el nombre de la persona cuyo cumpleaños fuera el más cercano a la fecha corriente.

Consideremos esta lista de nombres con sus respectivas fechas de nacimiento



Si la fecha corriente es el 7 de octubre, la persona cuyo cumpleaños es el más cercano es, obviamente, María. Si le fecha fuera el 10 de octubre, el próximo cumpleañero sería Pedro.

Hacer este cálculo con Excel parece sencillo, diferencia en días entre dos fechas, pero tiene sus bemoles.
Excel representa cada fecha con un número entero que indica la cantidad de días transcurridos desde el 1ro. de enero de 1900. Por lo tanto, no podemos usar SIFECHA o usar una resta para calcular el intervalo entre la fecha actual y la fecha del cumpleaños de los miembros de la lista.
Tampoco podemos representar fechas en Excel sin el año, por lo que explico más arriba. Así que nuestra aproximación al problema será convertir todas las fechas de nacimiento a fechas del año corriente. Esto lo hacemos con la función FECHA creando una columna auxiliar (Aux 1)



Ahora creamos la columna auxiliar Aux2 donde calculamos la diferencia con la fecha de referencia, que hemos puesto en la celda B9



En la columna Aux2 vemos claramente que María es la cumpleañera, ya que la diferencia es 0 (el cumpleaños coincide con la fecha de referencia).
Como no siempre hay alguien que cumpleaños, necesitamos una forma de encontrar quien es el más cercano. A esta altura de los acontecimientos estaríamos tentados a usar la función MIN() lo cual es una buena idea pero con un pequeño problema. Pongamos como fecha de referencia el 10 de octubre



Si observamos con atención veremos que el próximo cumpleaños es el de Pedro. Pero si usamos el resultado de MIN(D2:D6) para encontrar el cumpleañero, el resultado será José ya que -252 es el menor de los números en el rango. Para corregir esto tenemos que sumar 365 (un año, expresado en días) a los resultados de Aux2 cuya fecha de nacimiento ya ha pasado.
En lugar de esto, cambiaremos de aproximación. Lo que haremos es crear una columna auxiliar donde calculamos la fecha (incluido el año) del próximo cumpleaños. Para esto usamos esta fórmula

=FECHA(2008+(C2<$B$9),MES(B2),DIA(B2))



La expresión lógica (C2<$B$9) calcula si la fecha del cumpleaños es anterior o posterior a la fecha de referencia. En caso de ser posterior (menor que la fecha de referencia) la expresión da VERDADERO que es sumado como 1 al año corriente.

Con esta aproximación, la columna Aux2 es innecesaria. Para obtener el nombre del cumpleañero usamos esta fórmula con INDICE y COINCIDIR

=INDICE(A2:A6,COINCIDIR(MIN(D2:D6),D2:D6,0))



Podemos reducir el número de columnas auxiliares a una reemplazando C2 en la fórmula de Aux3 por la fórmula de Aux1

=FECHA(2008+(FECHA(2008,MES(B2),DIA(B2))<$B$9),MES(B2),DIA(B2))



Otra corrección necesaria, si queremos usar este modelo también el próximo año, es reemplazar el "2008" en la fórmula por un calculo dinámico del año corriente

=FECHA(AÑO(HOY())+(FECHA(AÑO(HOY()),MES(B2),DIA(B2))<$B$9),MES(B2),DIA(B2))

Finalmente, si queremos prescindir totalmente de columnas auxiliares, podemos usar esta fórmula matricial (matricial = introducir pulsando simultáneamente Ctrl+Enter+Mayúsculas)

={INDICE(A2:A6,COINCIDIR(MIN(FECHA(AÑO(HOY())+(FECHA(AÑO(HOY()),MES(B2:B6),DIA(B2:B6))<$B$9),MES(B2:B6),DIA(B2:B6))),FECHA(AÑO(HOY())+(FECHA(AÑO(HOY()),MES(B2:B6),DIA(B2:B6))<$B$9),MES(B2:B6),DIA(B2:B6)),0))}

Technorati Tags: MS Excel