Tabla calendario completa con una sola fórmula de desbordamiento

Héctor lanza el reto: ¿se puede crear una tabla calendario completa (año, mes, nombre del mes, día, nombre del día, fecha formateada) con una sola fórmula que se desborde automáticamente? Lleva un día intentándolo con 6 fórmulas separadas.

Nacho comparte la fórmula base usando LET + SECUENCIA + TEXTO + APILARH:

``
=LET(
Ano; 2024;
Primerdia; FECHA(Ano; 1; 1);
UltimoDia; FECHA(Ano; 12; 31);
Calendario; SECUENCIA(UltimoDia - Primerdia + 1; 1; Primerdia; 1);
Atributos; {"aaaa"\"mm"\"mmmm"\"d"\"dddd"\"dddd, d \d\e mmmm \d\el aaaa"};
Columnas; TEXTO(Calendario; Atributos);
APILARH(Calendario; Columnas)
)
`

El truco clave: TEXTO(Calendario; Atributos) con un array horizontal de formatos genera todas las columnas de golpe. Cada formato de texto se aplica a todos los días simultáneamente.

La comunidad extiende la fórmula con campos adicionales:

Trimestre (Perú): usando COINCIDIRX con el mes del período:

`
=COINCIDIRX(MES(_fecha); {3; 6; 9; 12}; 1)
`

Quincena — tres enfoques distintos:

Con MAP + COINCIDIRX + FIN.MES (Perú):
`
=MAP(fechas; LAMBDA(r;
2(MES(r) - 1) + COINCIDIRX(r; FIN.MES(r; {-1; 0}) + {15; 0}; 1)
))
`

Con MAP simplificado (México):
`
=MAP(fechas; LAMBDA(r; 2(MES(r) - 1) + (DIA(r) > 15) + 1))
`

Sin MAP ni LAMBDA (John): la versión más corta:
`
=2 * MES(fechas) + (DIA(fechas) > 15) - 1
``

Un caso que empieza con una fórmula potente de Nacho y la comunidad la lleva al siguiente nivel añadiendo trimestres, semestres y quincenas.

El reto: una tabla calendario entera con una sola fórmula

Héctor lanza el desafío después de un día peleándose con el problema: quiere generar una tabla calendario completa — año, mes, nombre del mes, día, nombre del día y fecha formateada — que se desborde sola a partir de una única fórmula. Lo tiene resuelto con seis fórmulas separadas y no le convence.

La tabla calendario es una de esas piezas que aparecen en cualquier modelo de datos serio: es la que permite agrupar por trimestres, comparar años o filtrar por día de la semana sin que cada informe se invente sus propias columnas. Que se genere sola, y no a base de arrastrar, cambia bastante el mantenimiento.

La fórmula base

Nacho comparte el esqueleto, apoyado en LET para que se pueda leer:

=LET(
    Ano; 2024;
    Primerdia; FECHA(Ano; 1; 1);
    UltimoDia; FECHA(Ano; 12; 31);
    Calendario; SECUENCIA(UltimoDia - Primerdia + 1; 1; Primerdia; 1);
    Atributos; formatos;
    Columnas; TEXTO(Calendario; Atributos);
    APILARH(Calendario; Columnas)
)

La lógica va de arriba abajo sin sorpresas: se fijan el primer y el último día del año, SECUENCIA genera todas las fechas intermedias empezando por Primerdia y avanzando de uno en uno, y APILARH pega al final las columnas de texto.

Donde aparece formatos va una constante matricial horizontal con los códigos de formato que quieras: el año en cuatro dígitos, el mes en dos, el nombre del mes, el día, el nombre del día y una fecha larga completa. Al escribirla en la fórmula real, los elementos van separados por el separador de columnas que use tu Excel; si prefieres evitar constantes escritas a mano, puedes montar esa fila con ENFILA sobre un rango de celdas donde tengas los formatos.

El truco que lo hace todo

La pieza que resuelve el reto cabe en una línea:

Columnas; TEXTO(Calendario; Atributos)

TEXTO acepta una matriz de formatos en su segundo argumento. Al cruzar una columna de 366 fechas con una fila de seis formatos, devuelve de una tacada una tabla de 366 por 6: cada formato aplicado a cada día.

Eso es lo que convierte seis fórmulas en una. No hay que repetir TEXTO por columna ni arrastrar nada: la propia forma de los argumentos genera la tabla.

Es el mismo principio que aparece una y otra vez en Excel moderno: si cruzas un vector vertical con uno horizontal, obtienes la matriz completa. Aquí se aprovecha para los formatos, pero funciona igual con criterios, coeficientes o cualquier otra cosa.

La comunidad amplía la tabla

Con la base montada, el hilo se llena de columnas extra. Y son buenas.

Trimestre

Desde Perú, con COINCIDIRX en modo aproximado:

=COINCIDIRX(MES(_fecha); {3; 6; 9; 12}; 1)

El tercer argumento a 1 significa "la coincidencia exacta o el siguiente elemento mayor". Así, los meses 1, 2 y 3 caen en la posición 1; los meses 4, 5 y 6 en la 2, y así hasta el cuarto trimestre. Elegante, y se adapta a cualquier otro agrupamiento cambiando los cortes.

Quincena, por tres caminos

Aquí el hilo se pone divertido, porque salen tres versiones del mismo cálculo.

Con MAP y FIN.MES, también desde Perú:

=MAP(fechas; LAMBDA(r;
    2*(MES(r) - 1) + COINCIDIRX(r; FIN.MES(r; {-1; 0}) + {15; 0}; 1)
))

Con MAP simplificado, desde México:

=MAP(fechas; LAMBDA(r; 2*(MES(r) - 1) + (DIA(r) > 15) + 1))

Y John remata con la versión más corta, que además prescinde de MAP y LAMBDA por completo:

=2 * MES(fechas) + (DIA(fechas) > 15) - 1

Merece la pena entender por qué la última es tan corta. Cada mes tiene dos quincenas, así que el número de quincena avanza de dos en dos con el mes. La comparación del día devuelve VERDADERO o FALSO, que en una operación aritmética valen 1 y 0, y eso desplaza el resultado a la segunda quincena cuando toca. El ajuste final coloca la cuenta empezando en 1.

Y hay algo más importante que la brevedad: al no usar MAP, la fórmula opera sobre toda la columna de golpe en lugar de ir fecha por fecha. En una tabla calendario de varios años, esa diferencia se nota.

Funciones clave

  • LET — nombra los pasos intermedios. Sin ella, esta fórmula sería ilegible.
  • SECUENCIA — genera la columna de fechas consecutivas. Como una fecha es un número, basta con darle el primer día y avanzar de uno en uno.
  • TEXTO — con una matriz de formatos devuelve varias columnas a la vez. Es la clave del caso.
  • APILARH — pega la columna de fechas con el bloque de columnas de texto.
  • COINCIDIRX en modo aproximado — convierte un valor en el tramo al que pertenece. Ideal para trimestres y escalas.
  • MAP con LAMBDA — recorre elemento a elemento cuando hace falta. Aquí resulta que casi nunca hace falta.
  • FIN.MES — devuelve el último día de un mes desplazado. Útil para cortes de quincena precisos.

Conclusión

De este caso salen dos cosas que valen para mucho más que un calendario.

La primera: para generar varias columnas de golpe, dale a una función un argumento en horizontal y otro en vertical. TEXTO con una fila de formatos es el ejemplo perfecto, pero el patrón es general.

La segunda la deja clara la evolución de la quincena: cuando una fórmula pide MAP o LAMBDA, merece la pena preguntarse si la operación no funcionaría directamente sobre toda la columna. Muchas veces sí, y entonces la fórmula queda más corta y más rápida.

Un reto que empieza con una fórmula sólida y acaba mejorado entre media docena de personas es exactamente para lo que existe la comunidad de InflueXcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel