Calendario Perpetuo desde antes de 1900

🔝To translate this blog post to your language, select it in the top left Google box.

Esta entrada del blog es la tercera de la trilogía sobre el cálculo de fechas anteriores al año 1900 con funciones en VBA. La trilogía explica:

A) Cómo calcular fechas antes de 1900 en Excel y VBA

B) Cómo calcular la edad y el día de la semana antes de 1900

C) Calendario Perpetuo desde antes de 1900

En esta entrada explicaré por fin cómo hacer un calendario perpetuo desde antes del año 1900, basado en el calendario de este artículo:

Calendario Perpetuo desde 1900 con eventos

Aquel calendario lo diseñé para fechas válidas de Excel, por lo que sólo admite días a partir del 1 de marzo de 1900.

Este nuevo calendario admite todos los días del Calendario Gregoriano, en el supuesto que la primera fecha gregoriana hubiera sido adoptada el día 1 de enero del año 100, que es la primera fecha posible en lenguaje VBA.

Son varios días de intenso trabajo para generar este calendario, que me he propuesto como un reto interesante, ya que no he visto publicado ninguno similar en Excel en ningún otro sitio.

Espero que este Calendario Gregoriano Perpetuo sea de utilidad a historiadores, escritores, científicos, anticuarios, genealogistas, expertos en pandemias, y a cualquiera que necesite manejar fechas gregorianas pasadas, presentes y futuras...

La apariencia de la interfaz gráfica de usuario y los controles son los mismos que los del calendario anterior (enlace aquí), por lo que no los voy a explicar de nuevo.

Como mejora, he añadido la posibilidad de cambiar el idioma desde el propio calendario (celda G3) y, sobre todo, la posibilidad de elegir el día en que comienza el calendario gregoriano (celda G2), ya que es diferente en distintos países o estados dentro de un mismo país.

Como se puede ver en la imagen, el 15 de octubre de 1582 fue el primer día del Calendario Gregoriano en Italia, España, Polonia, Portugal y en varios estados de USA: Texas, Florida, California, Nevada, Arizona y New Mexico. Al jueves -juliano- 4 de octubre de 1582 le sucedió el viernes -gregoriano- 15 de octubre de 1582. Así desaparecieron 10 días debido a que ya se habían contado de más en el Calendario Juliano, pues este calendario tenía un año regular de 365 días divididos en 12 meses y se agregaba un día bisiesto a febrero cada 4 años.

El Calendario Gregoriano fue introducido en Europa en 1582 y posteriormente fue admitido en muchos países. Se denomina así por ser su promotor el papa Gregorio XIII, quien promulgó su uso por medio de la bula Inter Gravissimas.

Las fechas de adopción del Calendario Gregoriano por países se pueden consultar en el siguiente enlace:

List of adoption dates of the Gregorian calendar per country

En el calendario, que se puede descargar más abajo, en la hoja 'Fechas' se han incluido las fechas más significativas de adopción del Calendario Gregoriano:

En la hoja 'Año>=100' con el calendario, debes seleccionar el primer día del Calendario Gregoriano de tu país o estado en la celda G2 del calendario, siendo válido este calendario a partir de ese día.

Todas las fechas anteriores al día de adopción del Calendario Gregoriano son inexactas, pues se aplicaba el Calendario Juliano y, por lo tanto, los meses representados son incorrectos, quedando marcados en rojo y en cursiva los días anteriores y en rojo el nombre del mes. Si el mes central contiene fechas julianas, el título (rango K2:Q2) está en fondo rojo y pone Error Calendario Gregoriano. Si todos los días del mes central son fechas gregorianas, el título está en fondo azul y pone Calendario Gregoriano.

Si se quiere simular el Calendario Gregoriano para todas las fechas posibles en VBA, se debe elegir el día 1 de enero del año 100, primera fecha "gregoriana" posible en VBA.

En los siguientes enlaces puedes estudiar fórmulas, conversores y calendarios con fechas "julianas" y "gregorianas" de otros autores, a los que agradezco que me hayan ayudado a comprender la complejidad de estas fechas:

• Conversor online de fechas gregorianas a su número de día juliano, y viceversa, obteniendo también el día de la semana. Es capaz de detectar que las fechas del 5 al 14 de Octubre de 1582 ¡no existen en el Calendario Gregoriano ni en el Calendario Juliano!

Conversión dinámica Día Juliano - www.ugr.es/~eaznar

• Fórmulas para la conversión de fecha gregoriana a día juliano.

Conversión de fecha a día juliano - agrupacionastronomicamagallanes.wordpress.com

• Descripción muy completa y pormenorizada en inglés del día juliano y algoritmos de conversión entre fechas gregorianas y julianas.

Julian day - Wikipedia

• Calendario Gregoriano y Juliano solapados y configurables.

Calendario Octubre 1582 - www.timeanddate.com

No he tratado de crear un Calendario Juliano porque las fechas julianas se escapan del alcance de las funciones de fecha en VBA, que son tratadas como fechas del Calendario Gregoriano ¡hasta donde yo las conozco! ¿o estoy equivocado?

Lo que sí he hecho ha sido indicar en rojo los meses y días que no se corresponden con el Calendario Juliano vigente hasta que se adoptó el Calendario Gregoriano en un país o estado determinado por la lista desplegable de la celda G2.

Por ejemplo, para el día 15 de octubre de 1582, que fue el primer día gregoriano proclamado en el mundo, ese mes de octubre fue muy raro, como se puede ver en la imagen de la izquierda obtenida de este enlace:

En la imagen de la derecha aparece ese mes, tal y como se muestra en la plantilla Excel que se puede descargar al final de este artículo.

Los días previos al 1582-10-15 (en formato AAAA-MM-DD, según ISO 8601 DATE AND TIME FORMAT) están marcados en rojo y en cursiva, para avisar que son días no válidos en el Calendario Gregoriano. Incluso puede ser que no hayan existido nunca, como pasa con los días 5 al 14 de octubre de 1582, que fueron eliminados tanto del Calendario Gregoriano (porque no existían antes de proclamarlo) como del Calendario Juliano (porque el último día de uso de ese calendario fue el 1582-10-04).

Lo que está claro es que el mes de octubre de 1582 en España, Portugal, Italia, Polonia, y en algunos estados de USA, fue el más corto de su historia, al tener únicamente 3 semanas de duración, o sea, 21 días en lugar de 31 días, como es habitual cualquier mes de octubre.

Lo mismo se puede decir para los demás días de comienzo del Calendario Gregoriano, que eliminaron de 10 a 13 días de su Calendario Juliano. Más días cuanto más tarde se cambió al Calendario Gregoriano, para compensar los días de más que llevaba acumulados el Calendario Juliano.

En el siguiente vídeo hago una presentación de este completo calendario. Sube el volumen para escucharlo.

Después de subir el vídeo he hecho una mejora para mostrar el día anterior o posterior de la tabla Fechas.

La fórmula matricial para encontrar el día anterior es:

{=MAX(SI(Rango_NSerie_Fechas<Rango_Día;Rango_NSerie_Fechas;-999999))}

La fórmula matricial para encontrar el día posterior es:

{=MIN(SI(Rango_NSerie_Fechas>Rango_Día;Rango_NSerie_Fechas;9999999))}

No voy a explicar aquí las macros necesarias para el funcionamiento y operación de este calendario ni a escribir un tutorial detallado. Quien tenga interés en estudiarlas y analizarlas puede hacerlo, ya que están abiertas sin contraseña y comentadas.

Tampoco voy a explicar las fórmulas empleadas, pues las hojas están protegidas sin contraseña, y ya me he extendido bastante explicando las principales claves de este artículo.

Para los que han leído hasta aquí, lo que si tengo que decir es cuál es el truco principal de este calendario para que sirva como Calendario Gregoriano desde el año 100 de nuestra era, si ficticiamente algún Papa del Siglo I hubiera promulgado una bula papal 1500 años antes de la que proclamó el Papa Gregorio XIII, por la que se abandonó el Calendario Juliano, utilizado desde que Julio César lo instaurara en el año 46 a. C., dando paso al vigente Calendario Gregoriano.

TRUCO DEL CALENDARIO

Haciendo clic en la fila 25 del calendario aparece un calendario auxiliar, en el rango de filas 27:34, con el que se calculan los números de serie de fechas VBA que, como ya sabemos, pueden ser negativos:

Gracias a trabajar con los números de serie de fechas VBA podemos generar calendarios que no están limitados a años a partir de 1900, como ocurre con los números de serie de fechas en Excel.

El rango permitido de fechas en VBA en formato ISO 8601 (AAAA-MM-DD) es:

• Día 0100-01-01: Núm. Serie VBA = -657434
• Día 9999-12-31: Núm. Serie VBA = 2958465

En este calendario auxiliar los números de serie mayores a 60 se muestran como el número de día del mes y para los números de serie menores a 61, incluidos todos los negativos, se muestra su valor con formato condicional en color rojo y letra cursiva, y con el formato de celdas de número personalizado: [>60]d;-0;0; 

Se han elegido números de serie menores de 61 porque las fechas de enero y febrero de 1900 se calculan también con números de serie VBA, pues son incorrectos los números de serie Excel para esos dos meses, ya que MS Excel considera erróneamente bisiesto el año 1900.

Descarga del calendario

Descarga este Calendario Gregoriano Perpetuo desde Google (con el botón "Excel Download") o desde el enlace a Microsoft OneDrive:

Calendario Perpetuo desde antes de 1900 PW1.xlsm 

Abre el archivo, permite su edición y habilita las macros para probar este singular calendario, que me he atrevido a diseñar al no encontrar en Internet ninguno parecido. Si sabes de alguien que haya publicado algún calendario similar en Excel, ¡por favor! ¡quisiera saberlo!

Este calendario viene con los Días de Independencia de varios países obtenidos de la Wikipedia (enlace aquí).

Copyright

Yo, Pedro Wave, estoy publicando bajo una licencia Creative Commons License: 

Attribution-NonCommercial-ShareAlike 3.0 Unported (CC BY-NC-SA 3.0)

https://creativecommons.org/licenses/by-nc-sa/3.0/

Los términos de la licencia son:

• Atribución: Otorgue el crédito apropiado, especialmente mantenga mi nombre y el nombre de mi blog en el libro de trabajo y el código.
• Compartir igual: Regalarlo gratis. Lo estoy regalando gratis, así que también tiene que hacerlo igual.
• No comercial: No use mi plantilla y código para crear una plantilla o libro de trabajo para la venta comercial.

El próximo artículo será sobre un Control de Calendario con Fechas y Horas, seleccionadas simulando ruletas por cada par de cifras, que publicaré como un complemento (Add In) en Excel:

Lo que ya no sé ahora mismo es si lo llegaré a publicar antes de que acabe este inusual año 2020, o al comienzo del próximo Año Nuevo 2021, que aprovecho para felicitar desde aquí a todos mis lectores, y que continuará con la nueva normalidad que nos depararán los años y siglos venideros, para los que está preparado este Calendario Gregoriano Perpetuo en Excel.

¡¡¡ Al menos hasta que acabe el año 9999 !!!

Calendario de Eventos en Excel

Para que el calendario laboral, que publiqué en los dos últimos artículos de este blog, sea usado como un calendario de eventos, he tenido que mejorarlo añadiendo una nueva hoja con un calendario trimestral para que, debajo de cada uno de los 3 meses, se puedan ver hasta 50 eventos por mes. ¿Te parecen suficientes?

Enlace a los tres articulos anteriores:

Calendario Laboral con Festivos Dinámicos

Calendario Laboral Dinámico en Excel

Calendario en Excel con una tabla dinámica

Calendario Trimestral

Con este cambio el calendario se puede convertir en un calendario de aniversarios, de cumpleaños, de campeonatos o de lo que se te ocurra.

El calendario laboral estaba programado para mostrar 4 descripciones de eventos al mes, lo que es poco si se quieren mostrar aniversarios o cumpleaños de la familia, amigos y conocidos, o desconocidos.

El calendario anual de eventos es el mismo que el del calendario laboral, con 4 eventos descritos por mes.

El calendario trimestral muestra hasta 50 eventos por mes, eligiendo el primero de los 3 meses con una segmentación de datos que va desde enero hasta octubre, en cuyo caso se muestra el último trimestre del año.

Tanto el calendario trimestral como el anual calculan el número de aniversarios para años iniciales mayores que 1901.

Tabla de Eventos

Ahora solamente tienes que editar la hoja de Eventos, protegida sin contraseña, para guardar tus fechas importantes que quieras recordar.

Para ordenar las fechas de menor a mayor sigue estos pasos:

1. Desprotege la hoja de Eventos.
2. Clic en la flecha hacia abajo en la celda A1 con las Fechas.
3. Desmarca el año 1900 para filtrarlo y pulsa el botón: Aceptar
4. Clic en la flecha hacia abajo en la celda A1 con las Fechas.
5. Ordenar de más antiguos a más recientes.
6. Clic en la flecha hacia abajo en la celda A1 con las Fechas.
7. Borrar filtro de "Fechas"
8. Protege la hoja de Eventos.

Esta tabla de Eventos tiene 6 columnas:

A - Fecha: con la fecha del evento para el año elegido:

=SI(Rango_Año<AÑO([@[Día Inicial]]);"";[@Fórmulas]+SI(Y(DIASEM([@Fórmulas];2)=7;[@[Trasladar al lunes]]="a");1;0))

Se tiene en cuenta si se quiere trasladar al lunes un evento que cae en domingo.

B - Tipos: Nacimiento; Conmemoración; Fallecimiento.

C - Fórmulas: La fórmula más común es:

=FECHA(Rango_Año;MES([@[Día Inicial]]);DIA([@[Día Inicial]]))

Otras fechas se obtienen con diversas fórmulas que se explican en este artículo:

Calendario Laboral con Festivos Dinámicos 

D - Día Inicial: Fecha de comienzo de la conmemoración o fecha de nacimiento o fallecimiento.

Si la fecha es anterior a 1901, se pone este año como inicial.

E - Eventos: Descripción del evento.

F - Trasladar al lunes: Si está chequeado con un desplegable.

En el calendario anual se verán los primeros 4 eventos de cadas mes. En el calendario trimestral se verán los primeros 50 eventos de cada mes.

Descarga del calendario

Descarga este calendario trimestral y anual desde Google (con el botón "Excel Download") o desde el enlace a Microsoft OneDrive:

Calendario_Eventos_PW1.xlsx 

Este calendario viene con varias conmemoraciones del mes de julio de 1957, para probar su funcionamiento, obtenidas de la Wikipedia (enlace aquí).

Espero que este calendario te sea últil y te ayude a confeccionar tus propios calendarios.

Calendario Laboral con Festivos Dinámicos

🔝To translate this blog post to your language, select it in the top left Google box.

Calendario Laboral (Continuación)

En el anterior artículo presenté un calendario laboral con festivos hecho con una tabla dinámica. Lo puedes ver en este enlace: Calendario Laboral Dinámico en Excel

Ahora voy a hacer que los días festivos sean dinámicos, para no tener que introducirlos manualmente cada año. Lo consigo usando fórmulas, que dependen del año elegido, en lugar de fechas estáticas.

Este calendario no contiene macros VBA, únicamente contiene fórmulas y una tabla dinámica. Puedes descargarlo al final de este artículo.

El único cambio necesario se ha hecho en la tabla de la hoja de Eventos, a la que se han añadido varias columnas:

A - Fechas: con las fechas de los eventos festivos. Si un festivo cae en domingo se traslada al lunes.

B - Fórmulas: con las fórmulas que calculan los días festivos. Luego las explicaremos detenidamente.

C - Tipos de festivos: Nacionales; Regionales y Locales.

D - Día D del festivo.

E - Mes del festivo.

F - Descripción del evento festivo.

En color amarillo he incluido los festivos de la ciudad de Zaragoza, Comunidad de Aragón, País España.

En color naranja hay algunos días que se celebran en los Estados Unidos de América - USA, ya que su cálculo es especial. Sigue leyendo.

¿Qué fórmulas calculan los festivos dinámicamente?

Todas las fórmulas nuevas están en la tabla de Eventos.

Se ha definido un nombre para el año seleccionado del calendario:

Rango_Año: =Calendario_Laboral!$K$2

En dos nuevas columnas se introduce el día inicial (columna D) y el mes (columna E) de la fecha festiva, excepto para los días del Jueves Santo y del Viernes Santo, para los que no se sabe anticipadamente su fecha.

La columna A calcula los días festivos a partir de la columna B con las fórmulas y, si cae en domingo, se traslada el festivo al lunes con la fórmula:

=[@Fórmulas]+SI(DIASEM([@Fórmulas];2)=7;1;0)

La columna B contiene las fórmulas que calculan los días festivos, para los que he contemplado varios casos:

1) Festivos normales: por ejemplo el día de Año Nuevo con la siguiente fórmula:

=FECHA(Rango_Año;[@Mes];[@Día])

Son todos los festivos de España, excepto los días de Semana Santa que son especiales.

2) Festivos en Semana Santa: el cálculo se hace con una fórmula muy curiosa que expliqué en este artículo: Cómputos que hacen la "Pascua"

Cálculo del Jueves Santo:

Thomas Jansen planteó esta curiosísima fórmula que funciona entre los años 1900 y 2203:

=MONEDA(("4/"&Rango_Año)/7+RESIDUO(19*RESIDUO(Rango_Año;19)-7;30)*14%;)*7-9

Esta fórmula es imposible de explicar y ni siquiera su autor lo hizo cuando la publicó en una competición que finalizó el 31 de marzo de 1999 para obtener el Domingo de Pascua (enlace aquí) 

Cálculo del Viernes Santo = un día más que el Jueves Santo: =B6+1

3) Festivos USA: el cálculo de los festivos de Estados Unidos de América lo he incluido por sus características especiales. En esta imagen se pueden ver las fórmulas de cálculo:

• Día de Martin Luther King: Se celebra el tercer lunes de enero.
• Día de los Presidentes: Se celebra el tercer lunes de febrero.
• Día Memorial o Día de los Caídos: se celebra el último lunes de mayo.
• Día de la Independencia: se celebra el 4 de julio.
• Día del Trabajo o Labor Day: se celebra el primer lunes de septiembre.
• Día de los Veteranos: se celebra el 11 de noviembre.
• Día de Acción de Gracias: se celebra el cuarto jueves de noviembre.

Para calcular estos días se usa la función FECHA con el año elegido, el mes del festivo y para el día se calcula con las funciones ELEGIR y DIASEM, que no voy a explicar aquí para que pienses un poco cómo se consiguen cumplir las reglas de celebración de estos días festivos...

Descarga del calendario

Descarga este calendario con festivos dinámicos desde Google (con el botón "Excel Download") o desde el enlace a Microsoft OneDrive:

Calendario_Laboral_Festivos_Dinámicos_PW1.xlsx 

Espero que me ayudes a encontrar si se me ha escapado algún error de cálculo de estos días festivos.

Si hay algún día festivo especial en tu localidad que no sabes cómo calcular con una fórmula, puedes escribir un comentario e intentaré ayudarte...

Calendario Laboral Dinámico en Excel

🔝To translate this blog post to your language, select it in the top left Google box.

Cómo crear un calendario laboral en Excel con una tabla dinámica

Visto el éxito que ha tenido mi anterior artículo:

Calendario en Excel con una tabla dinámica

Me he propuesto aprovechar el calendario de la tabla dinámica para crear un calendario laboral, importando las fechas dinámicamente.

En la siguiente imagen se ve el aspecto de este calendario laboral, con una segmentación para seleccionar el año, con los colores para distinguir los días festivos nacionales, regionales y locales, con el día de hoy coloreado en amarillo, con los fines de semana en rojo claro, con los 12 meses del año elegido y con hasta 4 festivos visualizados por cada mes.

Voy a explicar las características más relevantes de este calendario con la intención de que seas capaz por tí mismo de adaptarlo a tus necesidades o de crear tu propio calendario, ¿te parece?

Copyright

Yo, Pedro Wave, estoy publicando bajo una licencia Creative Commons License: 

Attribution-NonCommercial-ShareAlike 3.0 Unported (CC BY-NC-SA 3.0)

https://creativecommons.org/licenses/by-nc-sa/3.0/

Los términos de la licencia son:

• Atribución: Otorgue el crédito apropiado, especialmente mantenga mi nombre y el nombre de mi blog en el libro de trabajo y el código.
• Compartir igual: Regalarlo gratis. Lo estoy regalando gratis, así que también tiene que hacerlo igual.
• No comercial: No use mi plantilla y código para crear una plantilla o libro de trabajo para la venta comercial.

Descarga

Descarga este calendario laboral desde Google (con el botón "Excel Download") o desde el enlace a Microsoft OneDrive:

Calendario_Laboral_Dinámico_PW1.xlsx 

Vídeo con explicaciones

En este vídeo puedes seguir las explicaciones de cómo he creado este calendario:

Si después de ver el vídeo no te ha quedado claro o prefieres las explicaciones paso a paso, sigue leyendo.

Diseño Técnico

A continuación daré una explicación de cómo crear un calendario laboral a partir de una tabla dinámica.

Características más relevantes del calendario laboral:

1. Admite un rango de años desde 2020 hasta 2050.
2. Muestra los 12 meses del año como 4 trimestres.
3. El calendario se puede imprimir.
4. Los festivos nacionales, regionales y locales están coloreados y se describe cada día festivo.
5. El día de hoy está coloreado de amarillo, y se selecciona con un hipervínculo.
6. Se pueden mostrar u ocultar los números de semana, siempre que la hoja esté desprotegida. Las columnas con los números de semana están agrupadas:

• Nivel 1: Oculta las columnas B; K y T.
• Nivel 2: Muestra las columnas B; K y T.

Para diseñar este calendario hacen falta 4 hojas:

• 'Calendario_Laboral': con el calendario dinámico.
• 'Eventos': con la tabla de eventos, por ejemplo con los días festivos.
• 'Calendario Dinámico': con la tabla dinámica en forma de calendario. Se puede ocultar.
• 'Fechas': con la tabla de fecha que es el origen de datos de la tabla dinámica. Se puede eliminar.

Todas estas hojas están protegidas sin contraseña.

Hoja 'Fechas'

En esta hoja está el origen de datos de la tabla dinámica, como una tabla de 11.324 filas, con fechas del 1 de enero de 2020 al 31 de diciembre de 2050.

Hacen falta 3 campos para la tabla de fechas:

• Fecha: el primer día es el 1 de enero de 2020 y los demás se obtienen añadiendo un uno al día anterior: =A2+1
• Día de la semana: =DIASEM([@Fecha];2)
• Número de semana: =NUM.DE.SEMANA([@Fecha];2)

El segundo argumento vale 2 para que los días de la semana vayan del 1-lunes al 7-domingo.

Después de generar la tabla dinámica, esta hoja de fechas se puede eliminar para reducir el tamaño del archivo y aumentar su rendimiento.

Hoja 'Calendario Dinámico'

En esta hoja se ha creado un calendario a partir de una única tabla dinámica, con este aspecto:

Si quieres saber cómo crear este calendario dinámico debes leer este artículo de mi blog:

Calendario en Excel con una tabla dinámica

TRUCO: La segmentación de datos de "Años" sólo permite seleccionar un único año.

Si se selecciona más de un año, salta un mensaje de advertencia:

"No se puede cambiar parte de una celda combinada." 

Para continuar presiona el botón: Aceptar

Se han combinado celdas a propósito en la fila 94 para:

"NO PASAR DE ESTA FILA - Seleccionar un único año"

TRUCO: Con estas celdas combinadas se consigue que no pueda crecer la tabla dinámica y, por lo tanto, no se pueda seleccionar más de un año…

ATENCION: La hoja 'Calendario Dinámico' se puede dejar oculta por ser auxiliar para calcular los datos del calendario laboral.

Hoja 'Eventos'

Esta hoja contiene la tabla de eventos con 3 campos:

• Fechas: Con los días festivos.
• Tipos de fechas: Nacionales; Regionales y Locales.
• Eventos: Descripción del festivo.

Como ejemplo se han incluido los festivos de 2020 y 2021 para la ciudad de Zaragoza, Aragón, España.

Los eventos festivos pueden ser manualmente editados, añadidos y eliminados para incluir los festivos de tu pueblo o ciudad hasta el año 2050.

Hoja 'Calendario_Laboral'

El año del calendario se elige con la segmentación de datos de "Años".

La segmentación en 2 filas permite seleccionar un año del 2020 al 2050.

Como esta segmentación de años está conectada con la tabla dinámica que hemos visto antes, si seleccionas más de un año salta la advertencia:

"No se puede cambiar parte de una celda combinada."

Para continuar presiona el botón: Aceptar

Los nombres de los meses (con formato de celda: mmmm) se obtienen para el año 2000 (fuera del rango de años del calendario) con las fórmulas:

• enero: =FECHA(2000;1;1)
• resto de meses: =FIN.MES(B7;0)+1

Los nombres de los días de la semana se obtienen de la tabla dinámica con fórmulas del tipo: ='Calendario Dinámico'!C$3

Los números de semana se obtienen así:

• La primera semana de enero: =1
• La primera semana de febrero a diciembre es la penúltima semana del mes anterior, si no está completa, y es la última semana del mes anterior si la penúltima está completa: =SI(CONTAR.SI(C13:I13;0)>0;B13;B14)
• Para otras semanas se añade un 1.

ATENCION: Los días (formato de celda: d) se obtienen con la función para importar datos de la tabla dinámica:

=SI.ERROR(IMPORTARDATOSDINAMICOS("Fecha";'Calendario Dinámico'!$B$2; "Fecha";MES($B$7); "Día Semana";C$8; "Núm. Semana";$B9; "Años";$K$2);0)

Por ejemplo, para el mes de enero:

• El mes se obtiene de la cabecera con el nombre del mes. En este caso de la celda $B$7
• El día de la semana se obtiene de la fila con los nombres de los días, de "lun" (C$8) a "dom" (I$8).
• El número de semana se obtiene de la columna $B y filas 9 a 14.
• El año se obtiene de la celda $K$2 con el año elegido.

IMPORTANTE: Todas las fechas son fechas de Excel con sus números de serie. Que sean fechas de Excel ayuda a buscarlas en la tabla de festivos para aplicarles formatos condicionales.

En el menú: Fórmulas - Administrador de Nombres se pueden analizar los nombres definidos que se usan en las fórmulas de este calendario.

Comienzan por "Rango_"

Con 3 nombres se definen las columnas de la tabla de eventos: TablaEventos

Rango_Hoy llama a la función: =HOY()

Debajo de cada mes hay hasta 4 días festivos y su descripción, gracias a una fórmula matricial oculta (con formato: ;;;) en las columnas con los números de semana.

Las fórmulas matriciales se introducen presionando a la vez las teclas: Control + Mayúsculas + Intro

Aparecen automáticamente unos corchetes.

Fórmula matricial del rango B16:B19:

{=SI.ERROR(K.ESIMO.MENOR(SI($K$2 & MES(B7)=AÑO(Rango_Fechas) & MES(Rango_Fechas); FILA(Rango_Fechas)-MIN(FILA(Rango_Fechas))+1;""); FILA()-FILA(B15));0)}

El día festivo se obtiene con:

• C16: =SI(B16=0;""; INDICE(Rango_Fechas;B16))
• D16: =SI(B16=0;""; INDICE(Rango_Eventos;B16))

En la celda V4 (de color amarillo) hay una fórmula matricial oculta (con formato: ;;;):

{=DIRECCION( MIN(SI(Rango_Hoy=Rango_Calendario;FILA(Rango_Calendario);100)); MIN(SI(Rango_Hoy=Rango_Calendario;COLUMNA(Rango_Calendario);100)))}

Se obtiene un texto con la celda del día de hoy o $CV$100 si el año seleccionado no contiene hoy.

La fórmula con el hipervínculo al día de hoy está en la celda W4:

=HIPERVINCULO("[" & SUSTITUIR(CELDA("nombrearchivo");"[";"") & "!" & SI($V$4="$CV$100";"$T$2";$V$4);Rango_Hoy)

Al hacer clic en el hipervínculo se selecciona el día de hoy.

Con 3 formatos condicionales con fórmulas se colorean los días:

• Entresemana (blanco crudo): =Y(DIASEM(B7;2)<6;B7>40000)
• Fin de semana (anaranjado claro): =Y(DIASEM(B7;2)>5;B7>40000)
• Hoy (amarillo): =Y(B7=Rango_Hoy;B7>40000)

Con 3 formatos condicionales se colorean los tipos de días festivos. La fórmula es: =Y(B7>40000;SI.ERROR(INDICE(Rango_Tipos;COINCIDIR(B7;Rango_Fechas;0));"")=INDICE(Rango_Tipos_Fechas;n;1))

Siendo n:

• 1 - Nacionales: verde. 
• 2 - Regionales: azul.
• 3 - Locales: púrpura.

Las fórmulas se aplican a valores de celda >40000, por lo que no se aplica formato condicional a fechas anteriores al año 2010. Como se han usado fechas del 2000 para los nombres de los meses, éstos no cambian de color con el formato condicional.

NOTA: Se pueden cambiar estas celdas:

• B2: Calendario Laboral, con el nombre que quieras, por ejemplo: Calendario Fútbol
• T2: Zaragoza, con el nombre de tu pueblo o ciudad.

En estas 4 celdas se definen los tipos de días:

• Z63: Festivos
• Z64: Nacionales
• Z65: Regionales
• Z66: Locales

BONUS: Si quieres que sea un calendario con los partidos de fútbol de tu equipo favorito, edita las celdas:

• Z63: Fútbol
• Z64: La Liga
• Z65: Champions
• Z66: Copa del Rey

Y edita la hoja 'Eventos' con estos 3 tipos de fecha.

Agradecimiento

Para hacer este calendario me he inspirado en las siguientes páginas, a cuyos autores les estoy muy agradecido:

AyudaExcel.com Sergio Propergol  Plantillas calendario en Excel 2021

ExcelFORO         Ismael Romero    Calendarios en Excel. Fórmulas desbordadas

xelplus.com        Leila Gharani       Excel Calendar with Just ONE Formula!

Espero que este calendario anual, basado en una tabla dinámica, te sirva y te ayude a preparar tus propios calendarios.

Si encuentras una errata, avísame mediante un comentario para subsanarla en una próxima revisión. Gracias anticipadas por tus comentarios.

Tienes más ejemplos de mis calendarios publicados en esta página de mi blog:

https://pedrowave.blogspot.com/search/label/calendario

Prueba este nuevo calendario dinámico mucho más avanzado:

Calendario Laboral con Festivos Dinámicos | #ExcelPedroWave

Calendario en Excel con una tabla dinámica

🔝To translate this blog post to your language, select it in the top left Google box.

Cómo crear un calendario con una tabla dinámica

En este blog he publicado bastantes calendarios que se pueden ver en el siguiente enlace:

• calendario | #ExcelPedroWave

He buscado en la Web algún calendario basado en tablas dinámicas y no he encontrado casi nada. El calendario más parecido que he visto ha sido el del siguiente enlace, pero necesita demasiados campos en la tabla de origen de datos para generar la tabla dinámica.

• EXCEL UNIVERSITY - PivotTable Calendar 

Me he propuesto hacerlo con sólo 3 campos como origen de datos y que sea la tabla dinámica la que elabore el calendario, siendo éste el aspecto del nuevo calendario dinámico:

Con dos segmentaciones de datos, una para los años y otra para los meses, se puede configurar dinámicamente el calendario perpetuo, abarcando los siglos XX y XXI del calendario gregoriano.

En este vídeo puedes seguir paso a paso las explicaciones de cómo crear el calendario dinámico:

Aumenta el volumen para escuchar las explicaciones, pues la grabación de audio está a un nivel muy bajo. Disculpa las molestias. Para ayudar a seguir la explicación paso a paso he usado un cuadro de texto que muestra cada uno de los pasos, lo que espero sirva de ayuda para que puedas reproducir las explicaciones y consigas hacer tú mismo el calendario con una tabla dinámica, que es lo que pretendo con este artículo y el vídeo que le acompaña.

Paso a paso de cómo crear el calendario

En una hoja llamada 'Fechas' se crea una tabla con 3 columnas: Fecha; Día Semana; Núm. Semana.

Hay que rellenar la columna de Fecha con una secuencia desde el 01-01-1901, día de comienzo del siglo XX. El año 1900 es bisiesto erróneamente en Excel, por lo que no será incluido en el calendario.

Una forma elegante de rellenar las fechas es usando la función:

=SECUENCIA("01-01-2101"-"01-01-1901";1;"01-01-1901")

ATENCIÓN: Esta función sólo sirve en las últimas versiones de Excel 365; Web; iPad; iPhone y Android.

Por compatibilidad con versiones desde Excel 2010, es mejor introducir el primer día en la celda A2 y los sucesivos calcularlos con la fórmula: =A2+1

Se trata de rellenar todas las fechas de los siglos XX y XXI.

Son un total de 73.049 días obtenidos con la fórmula: ="01-01-2101"-"01-01-1901"

ATENCIÓN: En una hoja la máxima fecha sería el 24-11-4770: =FILA(A1048576)-1

Esta tabla de fechas será el origen de datos de la tabla dinámica que se creará en la hoja 'Calendario'.

Descarga esta plantilla vacía con los pasos a seguir desde Google (con el botón "Excel Download") o desde el enlace a Microsoft OneDrive.

Activa las macros y haz clic en la caja de texto.

  Calendario Dinámico PW0.xlsm 

Pasos a seguir ya explicados en el vídeo

1. Comienza en una nueva hoja, cambiando su nombre a: Fechas
2. Ponla en color amarillo.
3. Edita la tabla de fechas:
4. En A1 escribe la cabecera: Fecha; Día Semana; Núm. Semana
5. En A2 pon el día 01-01-1901
6. En A3 escribe la fórmula: =A2+1
7. En el menú Vista: Desmarca la Línea de cuadrícula
8. Inmoviliza la fila superior
9. Inserta la tabla con cabecera: Edita su nombre: TablaFechas
10. Cambia su tamaño a: 73050 filas
11. Cambia su formato a: Fecha corta
12. ATENCIÓN: Comprueba que se ha rellenado hasta el último día: 31-12-2100
13. Calcula el día de la semana en B2: =DIASEM([@Fecha];2)
14. NOTA: El segundo argumento indica con un 2 que la semana comienza en lunes (1) y acaba en domingo (7).
15. Calcula el número de semana en C2: =NUM.DE.SEMANA([@Fecha];2)
16. NOTA: El segundo argumento indica con un 2 que la semana comienza en lunes.
17. Copia las celdas B2 y C2 hacia abajo con autorrelleno.
18. Centra y ajusta las columnas.
19. Copia las fórmulas dinámicas de la tabla como valores estáticos.
20. NOTA: Este paso es recomendable si no va a crecer esta tabla, ya que mejora su rendimiento al no tener que recalcularla.
21. Inserta la tabla dinámica en una nueva hoja a partir de esta tabla.
22. Cambia el nombre de la hoja a: Calendario
23. Ponla en color azul.
24. Selecciona todas las celdas (arriba a la izquierda de las filas y columnas)
25. Selecciona menú de Inicio: Alineación
26. Centrar y Alinear en el medio
27. Selecciona "Fecha" como: Etiqueta de fila
28. Agrupa las fechas por: Meses y Años.
29. Selecciona "Núm. Semana" como: Etiqueta de fila
30. Selecciona "Día Semana" como: Etiqueta de columna
31. Selecciona "Fecha" como: Resumen de Valores
32. En Valores: Cuenta de Fecha Cambia la Configuración de campo de valor…
33. Resume el campo de valor por: Máx.
34. ATENCIÓN: Este es el truco principal de este calendario.
35. Valores como: Máx. de Fecha
36. ¡¡¡ Y se ven los números de serie de las fechas en Excel !!!
37. En el menú Vista: Desmarca la Línea de cuadrícula
38. Selecciona la fila 4: Inmoviliza paneles
39. En Herramientas de tabla dinámica: Diseño
40. Totales generales: Desactivar para filas y columnas
41. ¡Ya tenemos el esqueleto del calendario montado con una tabla dinámica!
42. Cambia los nombres de las etiquetas:
43. B2: Máx. de Fecha por: Calendario
44. B3: Etiquetas de fila por: Año / Mes / Semana
45. C2: Etiquetas de columna por: Día
46. C3 a I3: Cambia 1 por lun; … ; 7 por dom
47. Selecciona la celda B4 (1901):
48. Haz clic con el botón derecho del ratón y aparece el menú contextual.
49. Desmarca: Subtotal "Años"
50. Selecciona la celda B5 (ene):
51. Haz clic con el botón derecho del ratón y aparece el menú contextual.
52. Desmarca: Subtotal "Fecha"
53. Selecciona la celda D6 con el primer día del calendario:
54. Haz clic con el botón derecho del ratón y aparece el menú contextual.
55. Selecciona: Configuración de campo…
56. Haz clic en el botón de abajo a la izquierda: Formato de número
57. Selecciona Categoría: Personalizada
58. Cambia el Tipo: Estándar por la letra: d
59. ¡Perfecto! ¡Has conseguido que los valores de fechas se muestren como los días del mes!
60. Vamos a conseguir que el calendario sea dinámico.
61. En Herramientas de tabla dinámica: Opciones
62. Inserta Segmentación de datos
63. Marca: Años y Fecha
64. Selecciona la segmentación de datos: Fecha
65. En Herramientas de Segmentación de datos: Opciones
66. Cambia el Título de Fecha por: Meses
67. Cambia el número de columnas a 12
68. Sitúa esta segmentación arriba
69. Amplia su anchura hasta que se vean los nombres de los meses.
70. En Herramientas de tabla dinámica: Opciones
71. Desmarca: Botones +/-
72. Selecciona una celda de la tabla dinámica:
73. Haz clic con el botón derecho del ratón y aparece el menú contextual.
74. Selecciona: Opciones de tabla dinámica
75. Desmarca: Autoajustar anchos de columna al actualizar
76. Selecciona el rango de columnas C:I
77. Haz clic con el botón derecho del ratón para mostrar el menú contextual
78. Selecciona: Ancho de columna…
79. Pon un valor de: 8
80. En Herramientas de tabla dinámica: Diseño
81. Selecciona un estilo a tu gusto…
82. BONUS: Aplicar formato condicional a los días de entresemana:
83. Selecciona el rango de celdas: =$C$4:$I$15094
84. En el menú Inicio: Formato condicional
85. Haz clic en: Nueva regla
86. Utiliza la fórmula: =DIASEM(C4;2) 6
87. Formato: Color azul claro
88. Haz clic en el botón: Aceptar
89. BONUS: Aplicar formato condicional a los sábados y domingos (findes):
90. Selecciona el rango de celdas: =$C$4:$I$15094
91. En el menú Inicio: Formato condicional
92. Haz clic en: Nueva regla
93. Utiliza la fórmula: =Y(DIASEM(C4;2)>5;C4<>"")
94. Formato: Color rojo claro
95. Haz clic en el botón: Aceptar"
96. EXTRAS: Aplicar formato condicional al día de hoy:
97. Selecciona el rango de celdas: =$C$4:$I$15094
98. En el menú Inicio: Formato condicional
99. Haz clic en: Nueva regla
100. Utiliza la fórmula: =C4=HOY()
101. Formato: Color amarillo
102. Haz clic en el botón: Aceptar
103. Selecciona la celda B5 (ene):
104. Haz clic con el botón derecho del ratón y aparece el menú contextual.
105. Selecciona: Configuración de campo...
106. Selecciona la pestaña: Diseño e impresión
107. Marca: Insertar línea en blanco después de cada etiqueta de elemento
108. Con las segmentaciones de datos:
109. Selecciona el año: 2020
110. Selecciona 3 meses: de oct a dic
111. ATENCIÓN: Cómo reducir el tamaño del libro:
112. Selecciona la fila 27.
113. Mantén pulsada la tecla: Mayúsculas
114. Presiona una vez la tecla: Fin
115. Presiona una vez la tecla de cursor: Flecha hacia abajo
116. Haz clic en cualquier fila con el botón derecho del ratón
117. Haz clic en: Eliminar
118. IMPORTANTE: Guarda el libro inmediatamente.
119. IMPORTANTE: Elimina la hoja Fechas si no vas a ampliar el calendario a más siglos que el XX y el XXI. La tabla dinámica seguirá igual de dinámica y se reducirá el tamaño del libro y, por lo tanto, los tiempos para abrirlo y recalcularlo…
120. ¡¡¡ OBJETIVO CONSEGUIDO !!!

Si has seguido todos los pasos anteriores o has seguido las explicaciones del vídeo, ¡¡¡ ya tendrás un calendario dentro de una tabla dinámica !!!

Si no has seguido los pasos, o no lo has conseguido, puedes descargar esta plantilla desde Google (con el botón "Excel Download") o desde el enlace a Microsoft OneDrive, para jugar con este calendario:

  Calendario Dinámico PW1.xlsx 

Como aplicación de las tablas dinámicas he diseñado 3 calendarios más que te pueden interesar:

• Calendario Laboral Dinámico en Excel | #ExcelPedroWave
• Calendario Laboral con Festivos Dinámicos | #ExcelPedroWave
• Calendario de Eventos en Excel | #ExcelPedroWave

Comenta si ha sido una pérdida de tiempo o si has aprendido alguna técnica nueva que te haya servido para mejorar en tu dominio de Excel.

Calendario Perpetuo desde 1900 con eventos

0) Introducción al Calendario Perpetuo

Esta no es la tercera parte de la trilogía que estoy escribiendo sobre fechas anteriores al año 1900, sino que es un inciso para presentar un nuevo Calendario Perpetuo con fechas desde el año 1900, y que tiene como valor añadido la posibilidad de mostrar los eventos o efemérides de cada día pasando el ratón sobre las celdas. Accede al siguiente enlace en inglés para saber cómo:

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

La tercera entrega de la trilogía se basará en este calendario pero usando fechas en VBA, por lo que abarcará fechas desde el 1 de enero del año 100, con lo que servirá para todo el Calendario Gregoriano.

Este es el aspecto del nuevo calendario perpetuo:

1) Características relevantes

Características relevantes del Calendario Perpetuo en Excel:

1. Calendario Perpetuo Gregoriano con un rango de fechas del 01-03-1900 al 31-12-9999.
2. Vista de 3 meses: el mes seleccionado; el anterior y el posterior.
3. El primer día de la semana puede ser domingo o lunes.
4. Muestra u oculta los días de otros meses.
5. Sábados y domingos marcados en color rojo.
6. Flechas para incrementar o decrementar el mes, rotando como un carrusel.
7. Flechas para cambiar el año.
8. Selección de un mes.
9. Selección de una año mediante 2 cifras para las centenas y 2 cifras para las unidades.
10. Si se decrementa el año 1900 pasa al año 9999.
11. Si se incrementa el año 9999 pasa al año 1900.
12. Selección del día de hoy.
13. Selección del primer día del calendario perpetuo: 01-03-1900.
14. Selección del último día del calendario perpetuo: 31-12-9999.
15. Muestra u oculta un calendario auxiliar y otros datos.
16. Tabla de fechas con las efemérides guardadas, por ejemplo los Días de Independencia de los países obtenidos de Wikipedia (enlace aquí).
17. Hasta 12 efemérides por día en 3 páginas con 4 eventos cada una.
18. BONUS: Al pasar el cursor del ratón sobre una celda muestra los eventos de ese día.
19. Activa o desactiva el evento del ratón sobre una celda.
20. Botones de animación con avance y retroceso de los meses y con 5 velocidades de animación.
21. Botones para ver los primeros o los últimos 3 meses del año.
22. Botones para incrementar o decrementar de 3 en 3 meses.
23. Filtro de un día en la tabla de fechas.

No voy a explicar cómo usar este calendario, lo interesante es probar cada una de las características y experimentar con el diseño de la experiencia del usuario (UXD - User eXperience Design) de este calendario perpetuo.

Si tienes sugerencias para mejorar este calendario, puedes compartirlas escribiendo un comentario al final de este artículo.

2) Plantilla del Calendario Perpetuo desde 1900

Descarga la plantilla totalmente gratuita, con las macros visibles y las hojas protegidas sin contraseña, desde Google (con el botón "Excel Download") o desde el enlace a Microsoft OneDrive:

Calendario Perpetuo desde 1900 PW1.xlsm 

3) Cómo he diseñado este Calendario Perpetuo

Este calendario perpetuo se compone de 3 hojas:

• Año>=1900 - Donde se muestra y se controla el calendario.
• Fechas - Donde se guarda la tabla de fechas.
• Idiomas - Donde se guardan las traducciones de los idiomas.

En la hoja 'Idiomas' se selecciona en la celda A1 entre 2 idiomas: Español o English. Los textos de la columna A son las traducciones que aparecen en el calendario. Si quieres, puedes añadir más columnas con más idiomas. Una macro cambia los textos de la cabecera de la tabla de la hoja 'Fechas'.

La hoja 'Fechas' contiene la tabla de fechas en 3 columnas: Fecha; Evento y Núm. Serie VBA. Esta última columna es calculada con una macro y sirve para ordenar las fechas por su número de serie en VBA, o sea, con números positivos y negativos. He incluido algunas fechas significativas del calendario gregoriano desde 1900 y las efemérides de los Días de Independencia de varios países, obtenidos de la Wikipedia - enlace aquí. Desprotegiendo la hoja sin contraseña se puede editar cualquier fecha.

El calendario perpetuo está en la hoja 'Año>=1900'. Para explicar cómo ha sido diseñado nos centraremos en el mes central del calendario, en el rango K5:Q12

En la celda L5 se muestra el nombre del mes pero contiene el día 1 del mes y año seleccionados con alguno de los controles del calendario: con el desplegable de la propia celda; con el cambio de año; con las flechas de incremento o decremento de los meses; con las teclas de animación de la fila 14.

En el rango X34:Z46 está la tabla auxiliar con la lista de meses que se carga con la validación de datos de la celda L5.

El cálculo de un mes del calendario comienza en el calendario auxiliar que se encuentra entre las filas 23 y 30 (se puede ver haciendo clic en la fila 21).

En las celdas B25, K25 y T25 se calcula el primer día de la semana en que comienza un mes, con las fórmulas:

B25: =C5+1-DIASEM(C5;Rango_DíaSemana)

K25: =Rango_MesNom+1-DIASEM(Rango_MesNom;Rango_DíaSemana)

T25: =U5+1-DIASEM(U5;Rango_DíaSemana)

Siendo:

Rango_MesNom: ='Año>=1900'!$L$5

Rango_DíaSemana: ='Año>=1900'!$Z$2 (La semana comienza en: 1-dom; 2-lun)

Esas celdas contienen el número de serie de una fecha, por lo que el resto de los días se calculan a partir de esas celdas sumando un uno al día anterior. Se calculan 6 semanas para cubrir todo el mes. El formato de las celdas de esas fechas es una letra "d", por lo que se muestra el número del día. En los meses de las filas 23 a 30 se ven siempre los días de meses anteriores. Los días con eventos o efemérides en la hoja 'Fechas' se marcan en color naranja gracias al formato condicional. Los sábados y domingos se pintan en color rojo.

¡¡¡ Y ahora el truco fundamental !!!

¿Qué fórmula produce el efecto de pasar el ratón por encima de la celda ejecutando una macro?

Vamos a analizar una fórmula posible para la celda K8:

=HIPERVINCULO(MouseOver(K26);DIA(K26))

Hace referencia a la celda K26 del calendario auxiliar, que contiene una fecha cualquiera y llama a la función HIPERVINCULO que tiene 2 argumentos.

El segundo argumento es un nombre descriptivo que, en este caso, llama a la función DIA para mostrar el número del día en la celda.

El primer argumento es la ubicación del hipervínculo, ejecutando la macro MouseOver(K26), con la celda de la fecha auxiliar como argumento.

Esa era la fórmula original que sirve únicamente para días dentro del mes del calendario. La fórmula definitiva en la celda K8 es un poco más compleja:

=HIPERVINCULO(MouseOver(K26);SI.ERROR(SI(O($Z$3="ü";MES(K26)=$K$5);DIA(K26);"");""))

Esta fórmula sirve para todos los días del mes y tiene en cuenta si se muestran los días de otros meses (controlado por la celda Z3) y si hay errores en las fechas, cosa que sólo ocurre en enero de 1900 y en diciembre de 9999.

La macro MouseOver está en el módulo ModPasarRatónSobreCeldas

La función MouseOver devuelve una String con la ubicación de la propia celda en que se llamó, pero antes modifica la celda B16 ("Rango_Día") con la fecha del día sobre el que ha pasado el ratón por encima. Este efecto lo publicó por primera vez Jordan Goldmeier en su blog OPTION EXPLICIT VBA por lo que le estoy muy agradecido, pues ha contribuido a enriquecer la interactividad y usabilidad de Excel. En el siguiente enlace hay un ejemplo mío de lo que se puede llegar a hacer con este excelente efecto de pasar el ratón sobre las celdas de Excel sin tener que hacer clic en ellas:

pedrowave.blogspot.com - Como pintar con Excel

Las fórmulas que llaman a la función HIPERVINCULO consiguen cambiar el día en la celda B16, denominada "Rango_Día", con la macro MouseOver:

            Range("Rango_Día").Value2 = rCelda.Value2

¡MENUDO TRUCO!

En las filas 17 a 19 se muestran los eventos o las efemérides del día de 4 en 4 y hasta en 3 páginas, obtenidas de la tabla auxiliar en el rango B34:V46

La columna "Fila" contiene una fórmula matricial (introducida con las teclas: Control + Mayúsculas + Intro) para obtener 12 filas con los eventos del día extraidos de la tabla de la hoja 'Fechas':

{=SI.ERROR(K.ESIMO.MENOR(SI($B$16=Rango_Fechas;FILA(Rango_Eventos)-MIN(FILA(Rango_Fechas))+1;"");FILA()-FILA(B34));0)}

Haciendo clic en el día de la celda B16, si hay eventos en ese día, los filtra en la hoja 'Fechas'.

4) Próximos pasos

En una próxima entrega publicaré un calendario perpetuo similar pero usando las fechas de VBA en lugar de las de Excel, con lo que se podrán programar eventos desde el día 1 de enero del año 100.

Si te gustan los calendarios puedes leer todos los calendarios que he publicado hasta la fecha en este enlace:

https://pedrowave.blogspot.com/search/label/calendario