Calendario perpetuo en Excel sin macros

Aprende a crear un calendario perpetuo en Excel sin macros. Siguiendo los pasos del tutorial podrás crear un calendario dinámico que se actualiza automáticamente cuando seleccionas el mes y el año que quieres mostrar.

Aplica formato al área del calendario perpetuo

En primer lugar, debes delimitar el espacio que va a ocupar tu calendario. Debes seleccionar 6 filas, ya que, según el día de comienzo y fin de cada mes, como máximo un mes puede tener días en 6 semanas distintas. A estas filas aplica un alto de fila de 70.

Seguidamente selecciona 7 columnas y aplica un ancho de columna de 15.

Selecciona las celdas en las que se van a ubicar los días de tu calendario y aplica bordes en todos sus lados para poder visualizar bien el área.

En la parte superior crea un encabezado y escribe el nombre de cada uno de los días de la semana.

En la zona superior necesitas dos celdas para seleccionar el año y mes. Escribe las palabras año y mes en dos celdas contiguas. Debajo colocarás el valor correspondiente para el año y el mes.

En el caso del año lo escribirás directamente. Para los meses crea una lista desplegable. En otra zona de la hoja de Excel escribe todos los meses del año e inserta bajo la celda mes la lista desplegable.

Obtén del primer día del año y mes seleccionado

En una celda cualquiera, en el tutorial es la G3 vamos a insertar una función que nos devolverá la fecha del primer día del mes y año que esté seleccionado en las celdas de selección de la zona superior.

Para ello utiliza la función FECHA que devuelve el número que representa la fecha en código de fecha y hora de Microsoft Excel donde:

• Año será el que escribes en la celda de año D3.
• Mes corresponde al que seleccionas en la lista desplegable, para obtenerlo debes usar la función COINCIDIR donde:
  • Valor_buscado será el valor de la lista desplegable E3.
  • Matriz_buscada corresponde al rango de datos donde escribiste todos los meses.
  • Tipo_de_coincidencia será exacta, escribe 0 y cierra el paréntesis.
• Día queremos obtener el primer día, escribe 1.

Quedando la función de la siguiente forma:

=FECHA($D$3;COINCIDIR($E$3;$A$15:$A$26;0);1)

Obtener la posición del primer día dentro de la semana

En la celda contigua, H3, vas a obtener la posición del primer día del mes en la semana. Es decir, si ese cae en martes, miércoles, sábado, etc.

Para ello usarás la función DIASEM que devuelve un número del 1 al 7 que identifica el día de la semana donde:

• Núm_de_serie será la fecha del primer día de la semana calculado en la celda G3.
• Tipo, en este argumento debes seleccionar la opción 3 que numera números del 0 (lunes) al 6 (domingo). Seleccionamos este valor porque nuestra semana comienza en lunes.

Quedando la función de la siguiente forma:

=DIASEM(G3;3)

Automatiza el calendario perpetuo

En la celda correspondiente al lunes de la primera semana debes insertar una operación sencilla para obtener el día correspondiente según el mes. Que será la fecha inicial del mes calculada en la celda G3 menos la posición del primer día del mes calculada en la celda H3. Quedando =G3-H3.

Se mostrará la fecha completa correspondiente.

La fecha del martes de esa primera semana será igual a la fecha del día anterior más 1. Quedando =D6+1.

Arrastra la función del martes al resto de días de la primera semana.

A continuación, teniendo seleccionado los días de martes a domingo de la primera semana, arrastra la función hacia abajo para rellenar el resto de semanas del mes.

Para que se muestren las fechas correctas en el resto de celdas, la celda del lunes de la segunda semana será igual al valor de la celda del domingo anterior más 1. Quedando =J6+1.

Copia esa fórmula en el resto de lunes del mes para obtener la fecha correcta para cada uno de los días del mes de tu calendario.

Comprueba que cuando cambias el año y el mes en las celdas de selección, el calendario y cambia y funciona perfectamente.

Adapta el formato al de un calendario perpetuo

Ahora vas a aplicar el formato correcto para visualizar los días correctamente como en un calendario.

En primer lugar, cambiaremos el formato para que solo se muestre el día en las celdas del calendario. Selecciona todas las celdas correspondientes a los días.

En la pestaña de Inicio, ve a las opciones de número y clica en la flecha de la esquina inferior derecha para abrir la ventana de Formato de celdas.

Elige la categoría Personalizada y en el campo Tipo escribe d para que se muestre solo el día. Si en los días de un solo dígito (del 1 al 9) quieres que se muestre 01, 02, 03 etc, en el campo tipo debes escribir dd

Seguidamente, cambia la fuente, el tamaño y los colores de tu calendario para darle el aspecto que quieres. Puedes aplicar un color de fuente diferente a los sábado y domingos.

También aplica el formato deseado a las celdas donde seleccionas el año y el mes.

Sombrear días que no pertenecen al mes

Por último, vamos a aplicar un formato diferente a aquellos días que se muestran en el calendario pero que no pertenecen al mes mostrado.

Para ello usaremos el formato condicional. Selecciona todos los días del calendario. En la pestaña de Inicio, Formato condicional, Nueva regla.

Elige la última opción “Utilizar una fórmula que determine las celdas para aplicar formato” y escribe la siguiente función: =D6<$G$3

Donde D6 es la primera celda del calendario y G3 la celda donde obtuvimos el primer día del mes. Elige el formato y acepta.

Para sombrear los días al final del calendario selecciona de nuevo todas las celdas y de nuevo aplica una regla de formato condicional que será: =D6>FIN.MES($G$3;0). Elige el formato y acepta.

Ocultar datos auxiliares

Finalmente, si quieres puedes ocultar los datos auxiliares que has utilizado para crear tu calendario. Puedes seleccionar las celdas y aplicar un color de texto blanco para que no se visualicen, o bien selecciona las celdas y en las opciones de Formato de celda, en la categoría Personalizada, en el campo Tipo escribe ;;;