Calendario de varios años en una sola fórmula: una tira por mes, una fila por año y el nombre de cada día

Todo empezó con una pregunta sobre formato condicional: ¿es mejor una regla para todo el rango o una regla por tabla? Para probarlo, Miki montó un calendario dinámico: un mes debajo de otro, una fila por año, una columna por día y en cada celda el nombre del día de la semana. Lo hizo con doce PIVOTARPOR, una por mes, colocadas en bloques fijos. Funciona, pero si subes el número de años, los bloques se pisan.

La comunidad lo convirtió en un reto: todo el calendario en una sola fórmula, que crezca con los años que pongas en A2 sin romper nada.

Raúl lo resolvió con REDUCE: recorre los 12 meses, saca cada uno con PIVOTARPOR (año en filas, día en columnas, nombre del día como valor), le pone la cabecera y lo apila con una fila en blanco de separación. Funciona en cualquier Excel 365, sin la beta:

``
=LET(_num_años; $A$2; _año_inicio; AÑO(HOY());
_f_inicio; FECHA(_año_inicio; 1; 1);
_f_fin; FECHA(_año_inicio + _num_años; 1; 1);
_fechas; SECUENCIA(_f_fin - _f_inicio; 1; _f_inicio);
_res; REDUCE(0; SECUENCIA(12); LAMBDA(acc; m;
LET(_fechas_mes; FILTRAR(_fechas; MES(_fechas) = m);
_nom_mes; NOMPROPIO(TEXTO(FECHA(_año_inicio; m; 1); "mmmm"));
_cabecera; APILARH(_nom_mes; SECUENCIA(1; 31));
_datos_brutos; PIVOTARPOR(AÑO(_fechas_mes); DIA(_fechas_mes); TEXTO(_fechas_mes; "dddd"); CONCAT; 0; 0; ; 0);
_datos; EXPANDIR(EXCLUIR(_datos_brutos; 1); _num_años; 32; "");
_bloque; APILARV(_cabecera; _datos);
_separador; EXPANDIR(""; 1; 32; "");
SI(m = 1; _bloque; APILARV(acc; _separador; _bloque)))));
_res)
`

Leo recordó que «REDUCE y APILAR es ahora MAP y APLANAR»: con las matrices anidadas de la beta, MAP devuelve un bloque por mes y APLANAR los junta. Su primera versión fallaba en el febrero de los bisiestos (calculaba el fin de mes con el año en curso) y John lo arregló contando los días del mes con FECHA(año; mes; 32):

`
=LET(S; SECUENCIA; a; S(A2;; AÑO(HOY()));
APLANAR(MAP(S(12); LAMBDA(x;
LET(d; S(; 32 - MIN(DIA(FECHA(a; x; 32))));
{{NOMPROPIO(TEXTO(29x; "mmmm")); a; ""}\{d; SI.ERROR(TEXTO(1(a&-x&-d); "dddd"); "")}})));
""; 0))
`

Dos trucos que merece la pena guardar:

- 32-DIA(FECHA(año; mes; 32)) da los días que tiene el mes: el día 32 se desborda al mes siguiente y lo que se pasa es justo lo que le falta al mes para llegar a 32.
- TEXTO(29*m; "mmmm") da el nombre del mes a partir de su número: 29, 58, 87… caen siempre dentro del mes 1, 2, 3… del año 1900.

Miki añadió una variante con FIN.MES y juntó las tres soluciones en un mismo libro (el fichero del caso), ya calculadas, con el formato condicional. Y ahí está la respuesta a la pregunta del principio: con todo en un solo rango desbordado bastan dos reglas sobre el bloque entero, una para los números y los meses y otra para sábados y domingos.

Ojo: las versiones de Leo, John y Miki usan matrices anidadas y APLANAR, que necesitan la beta de Excel con la versión de compatibilidad 3 en el libro; sin ella dan #CALC!. La de Raúl funciona en cualquier Excel 365. Dentro de las llaves, en Excel en español las columnas se separan con \`.

Un calendario en Excel parece un ejercicio de principiante hasta que le pides tres cosas a la vez: que salga de una sola fórmula, que crezca solo cuando cambias el número de años y que el formato condicional no se rompa por el camino. Eso es lo que resolvió la comunidad de InflueXcel en un hilo que empezó con una duda de rendimiento y acabó con tres soluciones distintas, una de ellas válida para cualquier Excel 365.

El problema

Un miembro preguntó si el formato condicional va más rápido con una regla para todo el rango o con una regla por tabla. Para probarlo, Miki montó un calendario dinámico con esta forma:

  • Un bloque por mes, uno debajo de otro.
  • Dentro de cada bloque, una fila por año y una columna por día (del 1 al 31).
  • En cada celda, el nombre del día de la semana: lunes, martes…

Su primera versión usaba doce PIVOTARPOR, una por mes, cada una en un sitio fijo de la hoja. Funcionaba, pero tenía un límite claro: si subes el número de años, cada bloque crece hacia abajo y pisa al siguiente. El reto quedó planteado: el calendario entero en una sola fórmula, que admita los años que pongas en A2.

Solución paso a paso

1. Con REDUCE y PIVOTARPOR (cualquier Excel 365)

Raúl generó primero todas las fechas del periodo con SECUENCIA y después recorrió los doce meses con REDUCE. En cada vuelta:

  1. Filtra las fechas del mes con FILTRAR(_fechas; MES(_fechas) = m).
  2. Las pasa por PIVOTARPOR con el año en filas, el día del mes en columnas y TEXTO(fecha; "dddd") como valor, sin totales.
  3. Le pone una cabecera con el nombre del mes y los números del 1 al 31, y la rellena con EXPANDIR hasta 32 columnas para que todos los meses midan lo mismo.
  4. Lo apila debajo de lo acumulado con APILARV, dejando una fila en blanco entre mes y mes.

Como todo vive en un único rango desbordado, ya no hay bloques que se pisen: con 7 años sale un calendario de 7 filas por mes, con 3 años, de 3. Y no necesita ninguna función nueva.

2. Con MAP y APLANAR (beta de Excel)

Leo lo resumió en una frase: «REDUCE y APILAR es ahora MAP y APLANAR». Con las matrices anidadas, MAP puede devolver un bloque entero por cada mes y APLANAR los junta en una sola tabla, sin ir acumulando. La versión final, con el arreglo de John:

=LET(S; SECUENCIA; a; S(A2;; AÑO(HOY()));
    APLANAR(MAP(S(12); LAMBDA(x;
        LET(d; S(; 32 - MIN(DIA(FECHA(a; x; 32))));
            {{NOMPROPIO(TEXTO(29*x; "mmmm")); a; ""}\{d; SI.ERROR(TEXTO(1*(a&-x&-d); "dddd"); "")}})));
    ""; 0))

La primera versión de Leo tenía un fallo sutil: calculaba el último día del mes con el año en curso, así que en los años bisiestos febrero se quedaba en 28. John lo detectó y lo corrigió calculando los días del mes para cada año. Las celdas de días que no existen (el 30 de febrero, por ejemplo) quedan vacías gracias a SI.ERROR.

3. Con FIN.MES

Miki añadió una tercera variante que cuenta los días con MAX(DIA(FIN.MES(FECHA(a; x; 1); 0))) y juntó las tres soluciones en un mismo libro, el que puedes descargar desde el caso.

Dos trucos de fechas que conviene guardar

Cuántos días tiene un mes: 32-DIA(FECHA(año; mes; 32)). Excel no protesta si le pides el día 32: se desborda al mes siguiente. Si el mes tiene 31 días, cae en el día 1 del siguiente y 32-1 da 31; si tiene 30, cae en el 2 y da 30; en febrero de un bisiesto cae en el 3 de marzo y da 29.

El nombre del mes a partir de su número: TEXTO(29*m; "mmmm"). Las fechas son números: el 29 es el 29 de enero de 1900, el 58 cae en febrero, el 87 en marzo… y el 348 en diciembre. Multiplicar por 29 te deja siempre dentro del mes que buscas.

Y el formato condicional

La pregunta del principio tuvo respuesta práctica: con todo el calendario en un único rango desbordado bastan dos reglas sobre el bloque entero, una para los números de día y los nombres de mes y otra para los sábados y domingos. No hay doce rangos que mantener ni reglas que se quedan atrás cuando la tabla crece.

Funciones clave

  • PIVOTARPOR: monta cada mes como una tabla de años por días.
  • REDUCE: recorre los meses y va apilando los bloques.
  • MAP y APLANAR: la alternativa con matrices anidadas, más limpia, que necesita la beta.
  • EXPANDIR y APILARV: igualan el ancho de los bloques y los apilan con su separación.
  • FECHA, DIA y FIN.MES: calculan los días de cada mes, bisiestos incluidos.
  • TEXTO: da el nombre del día y del mes.

Un aviso: las versiones con APLANAR y matrices anidadas solo funcionan en la beta de Excel, con la versión de compatibilidad 3 activada en el libro; si no, devuelven #CALC!. En Excel en español, dentro de las llaves las columnas se separan con la barra invertida.

Conclusión

Un calendario sirvió de excusa para comparar tres formas de pensar la misma fórmula: acumular con REDUCE, mapear y aplanar con las matrices anidadas, o calcular el fin de mes de otra manera. Gracias a Miki por montar el reto y el fichero, a Raúl por la versión que funciona en cualquier Excel y a Leo y John por llevarla a la beta y cazar el error de los bisiestos.

Más casos con estas funciones

Más contenido de Excel en InflueXcel