Cómo crear un calendario que cambia dinámicamente en Excel

En este artículo, le mostraremos cómo crear un calendario que cambia dinámicamente en Excel.

Descargar Libro de trabajo

Rellenar variables

El primer paso es crear las variables que se utilizarán en las fórmulas. Estas variables deben proporcionar la información actual para que el calendario se pueda actualizar automáticamente. Aquí está la lista de variables:

• Fecha actual
• El número del día actual del mes
• El número del mes actual
• Año actual
• La fecha del primer día del mes actual
• El número del día actual de la semana
• El número de la semana actual del año

Usaremos las funciones TODAY, DAY, MONTH, YEAR, DATE, WEEKDAY y WEEKNUM. Brevemente, mientras que la función TODAY devuelve la fecha de las funciones día, DÍA, MES, AÑO, DÍA DE LA SEMANA y NÚMERO DE la semana analizan los valores de fecha respectivos. La función FECHA devuelve el valor de fecha del año, mes y día dados.

En la siguiente captura de pantalla, puede ver los nombres que hemos dado y las fórmulas que se generaron el 20 de noviembre de 2020.

Nombramos cada variable para facilitar nuestro trabajo al crear fórmulas. El siguiente paso para crear un calendario en Excel es crear un esquema para la interfaz de calendario.

Esquema para el calendario en Excel

Antes de crear las fórmulas reales que generan los días del mes, primero necesitamos colocar un esquema para ayudar a crear fórmulas y también proporcionar referencia visual.

Los días se colocarán en una tabla de 7 columnas y 6 filas. Necesitamos aumentar estos números en 2 para los títulos y las celdas auxiliares también. Mientras que los títulos de columna y fila incluyen días de semana para columnas y números de semana por filas, las celdas auxiliares serán números consecutivos a partir de 1.

El calendario podría verse como a continuación.

Use bordes o colores de fondo como desee. El punto importante es usar un número de semana dinámico en el título. Haga referencia a la función WEEKNUM para la primera semana y agregue 1 por cada semana consecutiva.

Vamos a continuar con la creación de un calendario en Excel por rellenar los números de los días.

Generar días

Si está utilizando un diseño similar y no quiere preocuparse por los detalles de la fórmula, no dude en copiar la fórmula a continuación. Luego péguelo en la celda superior izquierda en el área de día de su calendario y rellénelo para las celdas restantes. Las celdas G2 y E4 se refieren a las primeras celdas de las columnas auxiliares. Por lo tanto, es posible que deba ajustar estas referencias, a menos que la primera celda sea G4.

Ahora, echemos un vistazo más de cerca a la fórmula. Primero, la fórmula utiliza la función FECHA, que devuelve una fecha por el año, mes y día dados. Los primeros 2 argumentos son proporcionados por las variables YearNumber y MonthNumber que ya se han calculado.

La parte de día de la fórmula tiene como objetivo generar números de día en una base de 7, ya que hay 7 días en una semana. 7*($E4-1) parte de la fórmula verifique el número de semana en la columna auxiliar E, y genere una base, comenzando con 0 y aumentando en 7 cada vez.

Podemos aumentar o disminuir este número tanto como la diferencia entre el día de la semana de el primer día del mes por el día de la semana número. En otras palabras, estamos recibiendo el primer día del mes actual como punto de anclaje. Por ejemplo, es el día 7 de la semana (de lunes a domingo) del 1 de noviembre de 2020.

Si la semana de nuestro calendario asume que el lunes es el primer día de la semana, el lunes debe ser 6 días antes (=1-7) del primer día del mes. Por lo tanto, obtenemos números como los siguientes.

Agregar estos valores a 1 para cambiar los números. Por lo tanto, el domingo puede convertirse en el primer día como en el siguiente ejemplo.

Como resultado, tenemos 2 matrices de 2 dimensiones. Para crear una tabla, necesitamos agregar ambas matrices juntas.

Estos números representan el día del mes actual. Hay números menores de 1 o mayores de 31, que obviamente no pueden ser días del mes.

La función DATE maneja estos tipos de números generando una fecha en el mes anterior o el siguiente. Por ejemplo, -2 se convierte en dos días antes del primer día del mes dado.

Finalmente, si utiliza estos números en la función FECHA, puede obtener las fechas reales del mes actual.

Formatear

Números de día

Ahora, vamos a rellenar los días. Puede modificar el formato de los valores sin cambiar el valor real. Esta característica se denomina Formato de números. Siga los pasos para ajustar el formato de los números para mostrar solo los días.

1. Seleccione cualquiera de las fechas en el área de día
2. Presione Ctrl + 1 para abrir el cuadro de diálogo Formato de celdas
3. Active la pestaña Número si aún no está abierta
4. Seleccione Personalizado en la lista de categorías
5. Escriba la letra d en el cuadro Tipo
6. Haga clic en Aceptar para guardar
   

Ahora, el diseño del calendario está listo.

Si desea eliminar el relleno para fechas de un solo dígito, puede usar dd en lugar de d simple. Para obtener más información sobre el formato de números: Formato de números en Excel: Todo lo que necesita saber

Resaltar el día actual

Finalmente, usaremos el Formato condicional para establecer diferentes colores para días que no están en el mes actual y resaltar el día actual.

Para agregar estas opciones de formato condicional;

1. Seleccione el rango de días
2. Haga clic en el icono de Formato condicional bajo la pestaña Inicio de la Cinta
3. Seleccione Nueva regla
   
4. Seleccione Usar una fórmula para determinar qué celdas formatear
5. Ingrese una fórmula para definir la regla para resaltar el día actual
   =G4=TODAY()
   G4: La celda superior izquierda del rango incluye días. Utilice una referencia relativa(no utilice $).
6. Haga clic en el botón Formato en la ventana de vista previa del cuadro de diálogo Formato de celdas
   
7. En el cuadro de diálogo, seleccione las opciones de formato que desea ver en la celda actual
   Usamos un fondo naranja en este ejemplo.
8. Haga clic en Aceptar para aplicar
   
9. El cuadro de vista previa mostrará sus preferencias
10. Haga clic en ACEPTAR para aplicar formato condicional
    

Así es como se ve:

La fórmula devuelve un valor booleano. Si el valor de celda (G4) es igual al resultado de la función HOY, que devuelve el día actual en un formato de fecha, la fórmula devuelve VERDADERO. De lo contrario, FALSO. Si el resultado es VERDADERO, Excel aplica el formato a la celda.

Hay 2 puntos importantes aquí:

1. Debe usar una referencia relativa, por ejemplo, G4, a menos que no desee que Excel rellene la referencia a lo largo del rango. Considere cómo cambian las fórmulas cuando las copia en otro lugar. Excel también aplica lo mismo para las fórmulas de formato condicional. Para obtener más información, consulte: Cómo crear una referencia absoluta y una referencia relativa de Excel
2. Las celdas de día deben devolver los valores de fecha reales. Esta es la razón por la que estamos utilizando el formato de números para mostrar las porciones del día.