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

viernes, octubre 10, 2008

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:

17 comments:

David 13 octubre, 2008 12:10  

Cuando yo estudiaba economicas no recibimos ni una hora de excel .. ni de ingles ... y asi nos me va ahora con treintaipico y 20 empleados.

Me estoy enamorando de las posibilidades de excel, acabo de descubrir tu web y veo que te apasiona.

Gracias por lo que a mi me toca, me será muy util. Internet a veces si refleja la nobleza y desinteres del genero humano

Javier,  13 octubre, 2008 17:53  

Excelente blog.

Tengo una duda en Excel no directamente relacionada con este tema, pero no he encontrado un post donde ponerlo así que elijo este último:
Se trata de comparar una matriz de N columnas con una columna inicial de resultados, de modo que se obtenga para cada columna el nº de "aciertos" que son las celdas que coinciden con la celda correspondiente (misma fila) de la columna de resultados.
Lo he podido hacer pero a base de una larga cadena de sumas de SI(A=B;1;0), que resulta un tanto farragosa. ¿hay alguna manera más sencilla de hacerlo?

Muchas Gracias de antemano por tu tiempo.

Javier,  14 octubre, 2008 23:18  

Jorge, ya veo que la solución te parece evidente, pero yo sigo sin verla: pongo un CONTAR.SI bajo cada columna y pongo en cada uno como rango la propia columna , pero ¿y el criterio? ¿cómo hago que compare celda a celda con la columna de referencia?

Angelh...,  15 octubre, 2008 00:18  

Fantastico, Es Perfecto Encontrar a Alguien que se Apasiona Tanto Como Yo Con Microsoft Excel...
Te Felicito y Gracias por tus Aportes...

Jorge L. Dunkelman 16 octubre, 2008 21:48  

Javier

mandame el archivo por email para que pueda mostrarte en tu caso como hacerlo.

Anónimo,  24 octubre, 2008 13:30  

Hola Jorge.
Me ha servido mucho tu fórmula, pero has pensado en simplificarla un poco?
=INDICE(A2:A6,COINCIDIR(K.ESIMO.MENOR(365-(SIFECHA(A2:A6;HOY();"yd"));1);(365-(SIFECHA($A$2:$A$44;$F$1;"yd")));0))

Lo de K.Esimo.menor es por si quieres ver los siguientes cumpleaños, y el SIFECHA para reducir un poco la fórmula.
Enhorabuena por tu blog!

Jorge L. Dunkelman 25 octubre, 2008 10:39  

Gracias por el aporte. Me parece muy buena la idea e usar SIFECHA, pero pero me encuentro con varios problemascon tu fórmula. Hay referencias a las celda A44 y F1, que supongo que pertenecen a una tabla sobre la cual has probado la fórmula. He tratado de adaptarla a la del ejemplo, pero los resultados no siempre son exactos. El problema es que tomas 365 como número de día en un año, pero 2008 tiene 366 (año bisiesto).

Claudio Rodriguez 13 marzo, 2009 21:46  

Hola una consulta, tengo una planilla en la cual tengo unos 2500 contactos con la fecha de cumpleaños, todos los meses ingreso cambios en la misma, por lo cual cada vez que quiero ver quien cumple los ordeno, la consulta es, quiero saber como hacer una referencia en otra hoja que automaticamente me de los cumpleaños por mes, por ejemplo si ingreso nuevos contactos con fecha de marzo en la hoja principal esta se actualiza en la hoja de marzo. gracias claumaro@gmail.com Claudio Rodriguez

Jorge L. Dunkelman 14 marzo, 2009 15:34  

Claudio
no me queda claro lo que quieres hacer. ¿Hay una planilla con todo los nombres y además una lanilla por mes?
SI hay una sola planilla por qué no usar Autofiltro?

Anónimo,  16 octubre, 2009 10:24  

Hola Jorge:

Ya sé que a veces es mejor usar columnas auxiliares y que facilitan las operaciones. Pero ¿qué te parece si calculamos la fecha del próximo cumpleaños de esta manera, sin usar la columna Aux 1?

=FECHA(AÑO($B$9);SI(Y(MES($B$9)>=MES(B2);DIA($B$9)>DIA(B2));MES(B2)+12;MES(B2));DIA(B2))

O al revés:

=FECHA(AÑO($B$9);SI(Y(MES(B2)<=MES($B$9);DIA(B2)< DIA($B$9));MES(B2)+12;MES(B2));DIA(B2))

En el argumento MES estoy utilizando un SI para que agregue 12 sólo en el caso de que el díames de la persona sea menor que el diames expresado en B9.

En definitiva, como te expreso antes, siempre tengo la duda si es mejor o no poner una columna auxiliar. Supongo que dependerá de la necesidad de ayudar a los demás a entender el problema.

Gracias por tu tiempo. Un saludo,
Sergio

Anónimo,  16 octubre, 2009 10:34  

Hola de nuevo:
Pido disculpas porque no vi la finalización del ejercicio donde se resuelve de modo parecido lo que planteé en el anterior comentario. Incluso veo que con la fórmula matricial no hay ninguna columna auxiliar.

De todos modos, la duda de usar o no columnas auxiliares siempre está ahí.

Gracias again,
Sergio

Jorge L. Dunkelman 16 octubre, 2009 12:42  

Sergio, gracias por colaborar.
Como en muchos órdenes de la vida, no hay una verdad absoluta. Las columnas auxiliares te permiten un mejor control del cálculo, reducen la posibilidad de errores y permiten entender el proceso del cálculo también varios meses después de haber construido el cuaderno.

Anónimo,  01 diciembre, 2011 22:26  

y que si hay dos personas que cumplen años el mismo día?

Jorge L. Dunkelman 02 diciembre, 2011 17:04  

Podrías usar K.ESIMO.MENOR en lugar de MIN. Preveyendo que puedan haber, por ejemplo, hasta 3 personas que cimplan el mismo día pondrías tres fórmulas (para el menor, el segundo menor y el tercero).

Anónimo,  15 octubre, 2014 22:50  

COMO LE HAGO PARA QUE A PAREZCAN 2 O MAS PERSONAS "NOMBRE" EN LISTA DE CUMPLEAÑEROS, SOLO ME DEJA VER UNO. O DICHO DE OTRA FORMA, COMO HACER QUE APARENZACAN LOS CUMPLEAÑEROS DE UN MISMO DIA.

Jorge Dunkelman 16 octubre, 2014 20:16  

Fijate en el comentario del 01/12/2011 y mi respuesta.
También puedes usar una tabla dinámica.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP