Categorías
Excel

Calendario perpetuo en Excel para todos los años

La verdad es que he estado pensando bastante sobre cuál debería ser el primer post del blog pero me estoy dando cuenta de que tampoco importa demasiado porque es posible que sea uno de los que menos visitas reciba.

Como no quiero darle más vueltas porque quiero empezar a publicar ya, voy a escribir sobre lo que estoy pensando ahora mismo y eso es en quitarme una espinita por un post en otro blog: explicar cómo se hace un calendario perpetuo.

En este post en Botiguers Online os dejaba un calendario perpetuo en Excel pero sin explicar apenas nada sobre cómo se puede hacer un documento como este, así que manos a la obra:

Índice de contenidos

PRIMER PASO: UN POCO DE ORDEN

Lo primero que hago es crear doce espacios de 8 filas por 7 columnas, que es donde irá cada mes. Siempre tendrán 8 filas que servirán: una para el nombre del mes, la segunda para los nombres de los días de la semana y 6 más para poner los números de los días. Siempre utilizamos 6 filas para poner los días porque aunque en algún año concreto haya meses que tengan 5 semanas (y se podrían hacer con sólo 5 filas), seguramente en otros años, esos mismos meses, tendrán 6 semanas. Esto nunca nos pasará con febrero, que nunca llegará a tener 6 semanas, pero me he permitido la licencia de aplicarle también 6 filas para que todo el documento se vea más uniforme.

Bueno, total, creamos los espacios para los meses y les ponemos los titulitos del nombre del mes y los días de la semana y lo que nos queda es algo así:

Espacios de 8x7 celdas para cada mes
Espacios de 8×7 celdas para cada mes

Antes de empezar a poner fórmulas y a hacer cálculos, lo que he hecho es crear una parte al inicio de la hoja, en las primeras celdas, para que el usuario indique el año del que quiere hacer el calendario. He hecho el documento de tal manera que para calcular todo el calendario sólo sea necesario este dato, es decir, sólo hay que escribir el año del que queremos tener el calendario, nada más.

 

EMPEZAMOS EL CÁLCULO

Realmente, sólo necesitamos saber dos cosas para poder hacer el calendario de cualquier año: cuál es el primer día y si el año es bisiesto. Gracias a las fórmulas de Excel podemos saber los dos datos que necesitamos sin despeinarnos y esto es precisamente lo que he incluido en las primeras celdas; cuando el usuario indica el año, se calcula el primer día y se comprueba si es bisiesto o no:

Datos en las primeras celdas: año, primer día y bisiesto
Datos en las primeras celdas: año, primer día y bisiesto

Para saber el primer día del año basta con utilizar la fórmula “=FECHA”. Esta fórmula te crea una fecha si le indicas un año, un mes y un día. Lo que he hecho es ponerle como año el contenido de la celda B3, que es donde el usuario escribe el año del calendario e indicar que el día y el mes son “1”, así que, sorprendentemente, me encuentro con que en la celda B4 siempre me aparece la fecha 01/01 del año que el usuario indica.

Detalle del primer día del año
Detalle del primer día del año

Aún no tengo ni idea de si el 1 de enero será lunes, martes o qué pero es que primero necesito tener expresado en forma de fecha el primer día del año y luego ya veremos cómo averiguar en qué día cae…

 

AÑOS BISIESTOS

Seguimos con el segundo dato importante: saber si el año es bisiesto. Igual alguien se escandaliza porque hay alguna fórmula poray que te calcula si un año es bisiesto pero como no me sé todas las fórmulas, ni me apetece, a veces le doy un poco de rodeo a algunos temas. Lo que he hecho yo para saber si el año es bisiesto es utilizar la función “=RESIDUO” (esta le da risa a mi compañera Sílvia…).

La fórmula “=RESIDUO” te calcula el resto resultante de una división, o dicho con rigor científico: en una división, el último numerito al que le pintábamos una raya debajo y que molaba cuando era 0 porque te daba la sensación de haber hecho bien la división.

Lo que hago con la función “=RESIDUO” es aplicarla a dividir el año que indica el usuario (por ejemplo 2014) entre 4. Todos los años bisiestos son múltiplos de 4, por lo que siempre que se divide un año bisiesto por 4, el resto es 0. Ya me ves venir ¿no? Exactamente, si el resultado de la fórmula “=RESIDUO” es cero, el año es bisiesto y con cualquier otro resultado el año no es bisiesto.

Para finiquitar el tema del bisiesto, basta con poner una fórmula condicional (yo la he puesto en la celda C5) que ponga un 0 si el residuo es diferente a 0 y un 1 si el residuo es 0, así tengo un 1 si el año es bisiesto y un 0 si no lo es.

 

DATOS AUXILIARES

A la izquierda del nombre de cada mes he añadido los datos auxiliares que hacen referencia a ese mes y que son necesarios para que puedas calcular y situar bien los días: número de mes (del 1 al 12), los días que tiene y qué día de la semana corresponde al día 1.

Para poner el número de días de febrero es para lo que necesitas el dato de si el año es bisiesto o no, por lo tanto, lo que he hecho es añadir una fórmula en el número de días de febrero que pone “28” si el valor de la celda C5 es 0 (o sea, como te decía en el párrafo anterior, que el año NO es bisiesto) o “29” si el valor de la celda C5 es 1. Listo.

El único dato que se obtiene usando una fórmula específica es el de en qué día de la semana empieza el mes, es decir, saber “en qué cae” el día 1. Por suerte, como siempre, tenemos una fórmula que lo hace: “=DIASEM”.

Lo que he hecho es usar la función “=DIASEM” para saber a qué número de día (1=lunes, 2=martes…) corresponde el día 1 de cada mes. Como puedes ver en la imagen, lo que hago es crear una fecha del día 1 de cada mes usando la función “FECHA” con el año que ha indicado el usuario (en este caso, celda B3), el número de mes que tienes justo al ladito de donde estás y el número de día que siempre es 1.

Una vez creada la fecha, sólo tienes que incorporarla a la función “=DIASEM” y ésta te devuelve el número de día. Si no añades nada más a la función DIASEM te devolverá un número del 1 al 7 pero empezando por el domingo, de manera que 1=domingo, 2=lunes… Para que te devuelva el valor numérico en formato 1=lunes y 7=domingo, tienes que añadir como segundo argumento de la fórmula “,2” que corresponde al argumento “tipo” de la fórmula “=DIASEM”. Resumiendo, mira la foto de debajo y hazlo igual 😉

Datos auxiliares: saber "en qué cae" el día 1
Datos auxiliares: saber «en qué cae» el día 1

 

Hasta aquí el trabajo de cálculo, ahora tienes toda la información y sólo hace falta representarla en los espacios de cada mes del calendario para tenerlo listo.

 

RELLENANDO LOS DÍAS

Para rellenar las celdas con los días bien distribuidos y que nos quede un calendario chulo y que funcione para siempre, porque sino ya me contarás la gracia de llamarlo calendario perpetuo, sólo hace falta añadir 3 tipos de fórmulas más.

Para que te hagas una idea, te dejo una imagen coloreada según los diferentes tipos de fórmula que tienes que usar para que quede todo bien:

  • El color verde corresponde a la fórmula que tenemos que poner en todos los días 1 de todos los meses. Lo único que va a hacer esta fórmula es comprobar si el primer día del mes cae en lunes.
  • El color rojo corresponde a una segunda estructura de fórmula que he puesto sólo en la primera semana de cada mes, donde vamos comprobando si el día 1 cae en martes, miércoles, jueves… hasta el domingo.
  • El color amarillo corresponde al grueso de las casillas que no son primera semana y lo único que tienes que hacer es ir haciendo que siga la serie numérica, desde que localizas el día 1 hasta que llegas al final del mes.
Un tipo de fórmula en cada color
Un tipo de fórmula en cada color

 

LA PRIMERA SEMANA

En la primera semana de cada mes tienes un par de estructuras de fórmula diferentes. Como te acabo de decir, primero comprobamos si el día 1 cae en lunes y luego el resto de días.

Vamos a ver el lunes:

Lo resolvemos con una estructura de fórmula muy sencilla:

=SI([celda donde pone el número de día en que cae el día 1]=1,1,»»)

Si el número de día del día 1 es 1(lunes), la fórmula da como resultado un 1. Si es cualquier otro día, dejamos la celda en blanco.

A partir del martes sólo tienes que ir viendo si la celda anterior está en blanco y poner un 1 donde toque. A partir del momento en que encuentres el 1, tienes que ir sumando +1 hasta llegar al domingo. Mira el ejemplo del primer martes de enero sabiendo que I3 es el lunes y G1 es donde pone el número de día en que empezamos el mes:

=SI(I3=»»,SI(G1=2,1,»»),I3+1)

Si el lunes está vacío, compruebas si el primer día es el martes (G1=2) y si es así escribes un 1. Si no es así, dejamos la celda vacía. Si el lunes no está vacío (quiere decir que es lunes es día 1), entonces ponemos el valor del lunes+1, o sea, día 2.

¿Qué? Tampoco es para tanto, ¿no?

Te pongo un par de imágenes por si no te ha quedado claro o la explicación te lía un poco:

Primer lunes del mes
Primer lunes del mes
Primer martes del mes
Primer martes del mes

Para acabar la primera semana no tienes más que arrastrar la fórmula del primer martes hasta el primer domingo.

 

SEMANAS SIGUIENTES

En las cinco semanas siguientes he utilizado la misma fórmula. Lo que hago es sumar 1 al día anterior hasta llegar al número de días que tiene el mes.

Fíjate en la celda I4 (el segundo lunes de enero):

Segundo lunes de enero
Segundo lunes de enero

Compruebo que el domingo anterior (el 5 de enero en este ejemplo) no esté vacío ni sea día 31 y si se cumplen estos dos criterios (usamos la función “=Y” porque se tienen que cumplir los dos criterios, no sólo uno de ellos), sumo 1 al valor del domingo, o sea, 5+1 = 6.

Para los días siguientes sólo hay que ir repitiendo las fórmulas hasta llenar todo el mes. Verás que si has puesto la fórmula correctamente, los días irán apareciendo mientras arrastres la fórmula y pararán en el día del final del mes.

El único punto con el que tienes que tener cuidado al utilizar esta última fórmula es que cuando se trata de un lunes, el valor del día anterior está en domingo, es decir, en la fila superior, y de martes a domingo, el día anterior está en la misma fila en la que te encuentras.

La verdad es que lo más sencillo es que rellenes el mes de enero y copies y pegues las fórmulas en todos los demás meses. En este momento agradecerás mucho haberlos hecho todos de seis semanas de duración como te dije en el primer paso ;).

Hablando de copiar y pegar, también te recomiendo que cuando tengas la plantilla del calendario perpetuo funcionando correctamente y generes un calendario del año que quieras utilizar, lo copies y lo pegues en otro documento utilizando el pegado especial para no copiar las fórmulas y así poder trabajar tranquilamente con el calendario sin el riesgo de cargarte la plantilla.

 

YA TIENES UN CALENDARIO PERPETUO: REFLEXIÓN FINAL

A ver, una vez escrita toda la explicación, veo que he utilizado un concepto con el que puede que no estés familiarizado: anidar unas fórmulas dentro de otras. No te preocupes, no es difícil y le dedicaré un post completo con ejemplos y algunas aplicaciones pero, mientras tanto, si tienes alguna dificultad para hacer la plantilla del calendario perpetuo en Excel o simplemente no te apetece replicar lo que te he explicado en el post, te animo a que te suscribas al blog y recibirás inmediatamente la plantilla de calendario perpetuo en tu correo electrónico completamente acabada y operativa.

¿Me he dejado algo o hay algún punto que no se entienda bien? ¿Quieres preguntarme algo, insultarme un poco o darme las gracias? Lo mejor que puedes hacer después de suscribirte al blog es dejar un comentario y compartir este post en redes sociales. Quizá a ti no te parezca importante darle a “me gusta”, un +1 o un tweet pero a mí me da la vida y te lo agradeceré mucho 😉

Por Josep

Excel, inventarios, stocks, códigos de barras y muchas utilidades para tu ¿pequeño? negocio.

22 respuestas a «Calendario perpetuo en Excel para todos los años»

Wow! Yo me acuerdo e aquella vez que haciendo un calendario… se me acabaron las columnas del Excel jajajaja Por suerte era porque estaba guardando en version compatible con 2003 y al guargarla 2010 crecieron las columnas de forma magica.
Me parece fantastico que tengas un blog en el que expliques como formular este tipo de aplicaciones paso a paso! Quizas te preguntaria, o te daria como idea para que lo pusieras en cada post, por que se te ocurrio hacerlo? que utilidad le diste? Yo los calendarios los hacia para poner la dedicacion de cada persona al proyecto, con lo que lo usaba para gestion de proyectos. Por eso tenian otra forma.

¡Muchas gracias Ana por tu comentario! ¡Inauguras las aportaciones!
jejeje yo también recuerdo cuando en la época de Windows 95 dejaba pulsada la flecha hacia abajo del teclado para intentar saber cuántas filas tenía Excel…
Oye, pues muchas gracias por tu aportación, voy a intentar exponer un poco en los próximos posts cuál fue la motivación inicial para crear cada documento. Bueno, ahora me voy a echar un vistazo a leanlibelula.com
¡Saludos Ana, espero volver a verte por aquí!

Toma ya! pues pensando en el uso que le podría dar, creo que me va a ser de gran ayuda para llevar las facturas que van viniendo a cuenta gotas y desordenadas, para a fin de mes, hacer un sumatorio de todo y ver qué me falta (dinero, seguro) o qué me sobra (facturas) jajaja
Muy buen aporte!

Gracias por tu aporte, ahora podré hacerme el calendario, sin necesidad de esperar que alguien te lo pase, o pasando horas y horas configurándolo.

Sigue así, enseñándonos trucos.

Un saludo

SIMPLE CURIOSIDAD. ES ERROR LA «X» QUE APARECE O ES PARTE DEL CONTENIDO DEL CALENDARIO… PREGUNTA TONTA POR ASI DECIRLO JAJA
GRACIAS POR COMPARTIR TU CONOCIMIENTO… MUCHO EXITO!!!!

Necesito saber como hacer el formato condicional 🙁

Para finiquitar el tema del bisiesto, basta con poner una fórmula condicional (yo la he puesto en la celda C5) que ponga un 0 si el residuo es diferente a 0 y un 1 si el residuo es 0, así tengo un 1 si el año es bisiesto y un 0 si no lo es.

Muchas Gracias Josep!!. Conozco fórmulas sueltas, pero no sabía como organizarlas y hacer que funcionara. Tus explicaciones han sido geniales, Sigue asi!!!. Un abrazo.

Cordial saludo
Josep, envié vía correo un comentario respecto a los años bisiestos.
El tema es que como estaba la formula me daba los años bisiestos trocados cada dos años.
En la formula realmente realice dos cambios no uno como le escribí.
Esta es la formula original =SI(RESIDUO(B6;4)0;0;1) y esta es la que queda después de los cambios
=SI(RESIDUO(B6;4)=0;1;0) La pregunta es si es igual, no si es diferente a 0. Y que escriba 1 si el resultado es verdadero o 0 si es falso, antes estaba al contrario.
Suerte.

Josep, buenas noches
Me parece muy interesante la forma de como explica el diseño del calendario perpetuo. De igual forma me gustaría que me enviara la plantilla a mi correo.

Buenas tardes. Recién he encontrado la propuesta de calendario. De todas las que he visto me parece la mejor. Solo una petición: ¿cómo resaltar los sábados y domingos y algún día feriado? Felicitaciones y gracias.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *