Calendario perpetuo (o casi)

Sé que existen programas que permiten generar calendarios, sé que incluso algunos programas de office tienen asistentes para crear un calendario del mes que quieras, pero esto es un sitio web sobre Excel, y los visitantes suelen compartir una inquietud por hacer prácticamente de todo con la ayuda de Excel, así que los calendarios no van a ser menos.

Continúa leyendo y veremos paso a paso como construir un calendario mensual perpetuo (o casi)

La idea

  • Vamos a preparar un modelo que sirva para crear un calendario genérico.
  • Le indicaremos el mes y el año, y se recalculará para mostrar el calendario de ese mes.
  • Debemos poder indicar el primer día de la semana (en algunos países prefieren poner el domingo y en otros el lunes)
  • Nada de macros.

Aunque sea adelantar los acontecimientos, aquí pongo un vídeo del resultado final, para hacernos una idea mejor de a donde vamos a llegar

Manos a la obra

Datos de entrada

Empiezo preparando las celdas donde vamos a indicar el mes y el año.

Excel situó su inicio de fechas en 1900 (1904 si trabajas con una versión de Excel para Mac), así que para trabajar con fechas de Excel vamos a tener que poner este límite inferior; también tenemos un límite superior, porque Excel sólo admite fechas hasta el año 9999, con lo que nuestro calendario ya no es tan perpetuo como parecía, porque las fechas de Excel tampoco lo son.

Para simplificar la introducción de datos vamos a utilizar unas validaciones de datos.

La de los meses está clara, una lista del 1 al 12. Como no es probable que el número de meses cambie en los próximos años, pondremos la lista en la definición de la validación, en vez de ponerlo en celdas auxiliares.

Para el años vamos a poner una validación que impida introducir fechas inválidas incluso para los que han sucumbido al Mac. Para curarnos en salud, vamos a impedir que se elijan años anteriores a 1905, y que sean inferiores a 9999

Para el primer día de la semana añadimos una validación que permita elegir entre lunes y domingo.

Esquema del calendario

Vamos a preparar el aspecto que tendrá el calendario. Como mucho un mes tendrá 5 semanas, así que preparo una cuadrícula con siete columnas, una para cada día, con cinco filas para cinco posibles semanas, más una fila adicional para los títulos.

Como sólo quiero mostrar el número del día, voy a aplicar un formato dd a las celdas de lo que será el calendario.

Para las celdas de los títulos quiero que se muestre el nombre del día (lunes, martes,...) así que aplico un formato dddd a estas celdas para que las fechas se muestren con el nombre del día.

Como al principio de la primera semana puede haber días del mes anterior, y al final de la última semana puede haber días del mes siguiente, para facilitar la lectura vamos a aplicar un formato condicional que muestre esas fechas de otro color más claro.

Vamos con las fórmulas

La clave de todo el calendario está en encontrar el primer día de la semana en la que comienza el mes que hayamos elegido, el resto de fechas las obtenemos a partir de ese día.

Vamos a dejar esa fórmula para el final y empezamos por las fáciles:

sumando 1 al día anterior obtenemos la fecha del día.
sumando 7 al mismo día de la semana anterior obtenemos la fecha para esa semana.

las fórmulas podrían ser algo así:

¿y cómo calculamos el primer día del calendario?

Bueno, el primero del mes es fácil con la función FECHA(año_elegido;mes_elegido;1) el problema es que ese primer día del mes no es el primer día del calendario (puede ser un miércoles.

Lo curioso de esta función es que puedes elegir un número negativo o cero, por ejemplo el cero de enero es el 31 de diciembre, el -1 de enero es el 30 de diciembre, ...

Con ayuda de la función DIASEM podemos saber qué día de la semana es cualquier fecha, por ejemplo el primer día del mes.
esta función tiene un segundo argumento que permite elegir si la semana comienza el domingo o en lunes, este argumento lo expresaremos en función de la elección del primer día de la semana. El primero de mes sería algo así:

DIASEM(FECHA(año_elegido;mes_elegido;1);SI(primerdia_elegido="domingo";1;2))

La fecha del primer día de la semana en la que comienza el mes podemos conocerla calculando la diferencia entre el día uno del mes y el número de día que obtenemos con la función DIASEM (puede que se vea más claro con la fórmula)

=FECHA(año_elegido;mes_elegido;1)-DIASEM(FECHA(año_elegido;mes_elegido;1);SI(primerdia_elegido="domingo";1;2))+1

... y listo

En este vídeo puedes ver cómo funciona

Descarga el ejemplo

Si te gusta, puedes descargar el archivo de ejemplo de aquí. Puedes modificarlo a tu gusto.

Si "ya sabes Excel, pero necesitas más", mira este manual.

AdjuntoTamaño
File calendario_perpetuo.xlsx101.42 KB

Temática: 

Comentarios

Calendario

Imagen de Anónimo

Te expresas como un libro abierto campeón :)

Bastaría con arrastrar las

Imagen de pacomegia

Bastaría con arrastrar las fórmulas una fila más abajo.

Actualizo el archivo adjunto para este caso. Gracias por la puntualización.

 

Data Tools Suite
datos y tablas con Excel

calendario perpetuo

Imagen de Argeme

Muchas gracias por tú aportación, lo que andaba buscando, muchas gracias!!!