Calendarios en Excel. Fórmulas desbordadas

Cada vez es más frecuente encontrar por estas fechas publicaciones de todo tipo ofertando o simplemente mostrando como elaborar un calendario laboral para el próximo año...
En este mismo blog puedes encontrar ejemplos de calendarios perpetuos:
- con macros
- con fórmulas
- con Power Query

El día de hoy daremos una vuelta al asunto empleando las fórmulas desbordadas (de momento solo válidas para Microsoft 365).
Con nuestras fórmulas y un poco de formato condicional y formato personalizado llegaremos a:

Comenzaremos montado la formulación para enero, y posteriormente poder copiar y pegar para el resto de meses...
En primer lugar en B1 añadimos el año, en el ejemplo 2020, pero servirá cualquier año que quieras...
En B2 añadimos un 1 que representará el mes del año (ya sabes, 1 - enero, 2 - febrero, ..., 12 - diciembre).
En B4 la fórmula desbordada que mostrará la fechas del mes...
=SI(MES(SECUENCIA(6;7;FECHA($B$1;B2;1)-DIASEM(FECHA($B$1;B2;1);2)+1;1))<>B2;
"";
SECUENCIA(6;7;FECHA($B$1;B2;1)-DIASEM(FECHA($B$1;B2;1);2)+1;1))
La clave de la fórmula reside en la matriz secuencial devuelta por:
SECUENCIA(6;7;FECHA($B$1;B2;1)-DIASEM(FECHA($B$1;B2;1);2)+1;1)

con el que obtenemos una secuencia de fechas empezando con la fecha del lunes anterior al primer día del mes:
FECHA($B$1;B2;1)-DIASEM(FECHA($B$1;B2;1);2)+1
Matriz de seis filas (una por cada posible semana del mes) por siete columnas (una por cada día de la semana).
Acabamos nuestra función SECUENCIA indicando que el incremento o paso será de +1.

Con el condicional conseguimos 'dejar fuera de la ecuación', esto es, no mostrar las fechas que no correspondan al mes desplegado:
SI(MES(SECUENCIA(6;7;FECHA($B$1;B2;1)-DIASEM(FECHA($B$1;B2;1);2)+1;1))<>B2;"";...)

Incorporamos un encabezado en B3:H3 con los días de la semana (lunes, martes, miércoles, ...)

En C2 añadimos la función
=TEXTO(C6;"mmmm-aa")
para visualizar el mes de trabajo...

Y estariamos listos para perfilar el aspecto.
Primero aplicaremos al rango desbordado B4:H9 un formato personalizado de número tipo 'd'

Copiamos el rango trabajado hasta completar la distribución 'típica' de los doce meses

Ahora aplicaremos algo de formato condicional básico para resaltar los fines de semana, así como el día corriente y los días festivos (si disponemos de un listado de ellos!!)

Son tres reglas de formato condicional con fórmulas:
- B4=HOY() para marcar el día corriente
- DIASEM(B4;2)>=6 para resaltar los sábados y domingos
- COINCIDIR(B4;ndFestivos;0) para subrayar los días festivos

Para mi ejemplo previamente he creado una tabla con un nombre definido asignado 'ndFestivos'

Le quitamos las líneas de cuadrícula de las celdas y listo!!

Una variante de este calendario perpetuo sería mostrarlo en vertical

El montaje y formulación es casi idéntico (no podría ser de otra manera).
En B1 añadimos el año.
En B2 la función =SECUENCIA(1;12)
En A2:A45 la fórmula =TEXTO(SI(MAX(B4:M4)=0;"";MAX(B4:M4));"dddd")
que genera una secuencia de los días de la semana acorde a las fechas mostradas...
Y finalmente en B4
=SI(MES(SECUENCIA(42;1;FECHA($B$1;B2;1)-DIASEM(FECHA($B$1;B2;1);2)+1;1))<>B2;
"";
SECUENCIA(42;1;FECHA($B$1;B2;1)-DIASEM(FECHA($B$1;B2;1);2)+1;1))
fórmula que copiaremos para el resto de meses en C4, D4, ..., M4
La diferencia con el primer calendario es el tamaño del rango desbordado que devuelve SECUENCIA:
SECUENCIA(6;7) para el primer ejemplo
SECUENCIA(42;1) para este último

Aplicaremos los mismos formatos personalizados y formatos condicionales que en el caso anterior, y ya tenemos un calendario perpetuo 'vertical'.