Cómo crear un calendario semanal en una fórmula con las funciones de Excel 365. Este artículo es una idea de mi compañero MVP de Excel. leila garani.
La particularidad de un calendario semanal es que siempre comienza en lunes o domingo. Por lo tanto, necesito crear una fórmula que siempre devuelva el último lunes o domingo del mes anterior (si el mes no empieza en lunes ni domingo).
Paso 1: crea la fórmula para el primer día del calendario semanal de Excel
Para calcular el primer día de cualquier mes, necesitamos el función FECHA
=FECHA($C$2,$C$3,1)
Si personalizas el formato de fecha, puedes ver que el 1 de enero de 2020 es miércoles. Y si cambias el formato a Estándar, tienes el número 43831. Esta información es importante para la siguiente parte..
Paso 2: escriba el día del primer mes como encabezado
Podemos escribir esta fórmula como cabecera de nuestro calendario semanal.
Por supuesto, se debe personalizar el formato para devolver el nombre del mes y el año con este formato.
mmm aaaa
Paso 3: Cambia la fórmula para empezar siempre el mismo día de la semana (domingo, lunes,...)
Si deseas iniciar tu calendario semanal en domingo, debemos utilizar la función DÍA SEMANA y la función SI porque tenemos 2 situaciones:
- El primer día del mes es domingo, WEEKDAY(B5,2) = 7, por lo que nuestro calendario comenzará el primer día
- En caso contrario, reducimos el primer día del mes calculado en B5 por el número de días laborables para llegar al domingo.
Entonces, el domingo es:
=IF(WEEKDAY(B5,2)=7,B5,B5-WEEKDAY(B5,2))
Ahora, el 1 de noviembre de 2020 es domingo. Entonces, en esta situación, la fórmula vuelve al 01/11/2020.
Si tu calendario semanal comienza el lunes, la fórmula es:
=IF(WEEKDAY(B5,3)=7,B5,B5-WEEKDAY(B5,3))
Paso 4: Genera los demás días con SECUENCIA
Ahora necesitamos escribir todos los demás días de nuestro calendario semanal. Esto es posible con la fórmula de matriz dinámica SECUENCIA. Esta función tiene 4 argumentos.
- El número de filas (6 para cubrir toda la situación)
- El número de columnas (fácil, son 7, 7 días en una semana)
- El valor inicial es la fórmula anterior.
- Y el paso 1.
=SEQUENCE(6,7, IF(WEEKDAY(B5,2)=7,B5,B5-WEEKDAY(B5,2)) ,1)
Ahora bien, para entender cómo esto es posible, sólo debemos cambiar el formato numérico a Estándar para ver la lista de números generados por el función SECUENCIA
Paso 5: Personaliza el formato de la fecha
In este artículo, verás todas las explicaciones para cambiar el formato de fecha. Aquí queremos mostrar solo el valor del día. Entonces, en la configuración personalizada del formato numérico, escribiremos solo el valor d
Y ahora tenemos estos valores en el calendario semanal.
Paso 6: cambia el color del día dentro o fuera del mes seleccionado
Para tener una mejor visualización de los días del mes seleccionado, cambiaremos los colores de los días con formato condicional.
La regla para el día del mes seleccionado.
La primera regla para comprobar si las fechas están en el mes seleccionado es comparar si el mes de la fecha en B7 es igual al número del mes en C3. El formato a aplicar es color de fuente negro y texto en negrita.
=MES(B7)=$C$3
El $ para bloquear las referencias o no es muy importante aquí. MES(B7) es una referencia relativa para leer todas las celdas de nuestro calendario y se bloquea $C$3 en el mes seleccionado
La regla para los demás días, los que no están en el mes.
La fórmula para resaltar los días que no están en el mes seleccionado es muy fácil
=MES(B7)<>$C$3
El formato a aplicar en esta situación es un color de fuente gris.
La regla para los días de fin de semana.
La tercera regla, el color del día es el rojo y atrevido. Entonces la fórmula es
=Y(DÍA DE LA SEMANA(B7,2)>5,MES(B7)=$C$3)
El resultado final es tu calendario semanal
