EXCEL: Cuadrícula de calendario dinámico

Puede configurar una cuadrícula de calendario dinámico en una hoja de cálculo de Excel con una serie de fórmulas, como se explica en este artículo. En el ejemplo que se muestra, la fórmula en B6 es:

=start-ELEGIR(DIASEM(start),0,1,2,3,4,5,6)

donde "start" es el rango con nombre K5 y contiene la fecha 1 de septiembre de 2018.

Cómo funciona esta fórmula

Nota: Este ejemplo supone que la fecha de inicio se proporcionará como el primero del mes. Consulte a continuación una fórmula que devolverá dinámicamente el primer día del mes actual.
Con el diseño de la cuadrícula como se muestra, el problema principal es calcular la fecha en la primera celda del calendario (B6). Esto se hace con esta fórmula:

=start-ELEGIR(DIASEM(start),0,1,2,3,4,5,6)

Esta fórmula calcula el domingo anterior al primer día del mes mediante el uso de la función ELEGIR para "retroceder" el número correcto de días al domingo anterior. ELEGIR funciona perfectamente en esta situación, ya que permite valores arbitrarios para cada día de la semana. Utilizamos esta función para retroceder cero días cuando el primer día del mes es un domingo. Aquí se proporcionan más detalles sobre este problema.
Con el primer día establecido en B6, las otras fórmulas en la cuadrícula simplemente incrementan la fecha anterior en uno, comenzando con la fórmula en C6:

=SI(B6<>"",B6,$H5)+1

Esta fórmula prueba la celda inmediatamente a la izquierda para un valor. Si no se encuentra ningún valor, extrae un valor de la columna H en la fila de arriba. Nota $ H5 es una referencia mixta, para bloquear la columna a medida que la fórmula se copia en toda la cuadrícula. La misma fórmula se usa en todas las celdas, excepto B6.

Reglas de formato condicional

El calendario utiliza fórmulas de formato condicional que cambian el formato para sombrear los meses anteriores y futuros, y para resaltar el día actual. Ambas reglas se aplican a toda la cuadrícula. Para los meses anteriores y siguientes, la fórmula es:

=MES(B6)<>MES(start)

Para el día actual, la fórmula es:

=B6=HOY()

Para obtener más detalles, consulte: Formato condicional con fórmulas (10 ejemplos)

Rumbo del calendario

El título del calendario (mes y año) se calcula con esta fórmula en la celda B4:

=start

Formateado con el formato de número personalizado "mmmm aaaa". Para centrar el título sobre el calendario, el rango B4: H4 tiene la alineación horizontal establecida en "centrar a través de la selección". Esta es una mejor opción que combinar celdas, ya que no altera la estructura de la cuadrícula en la hoja de trabajo.

Calendario perpetuo con fecha actual

Para crear un calendario que se actualice automáticamente en función de la fecha actual, puede usar una fórmula como esta en K5:

=FIN.MES(HOY(),-1)+1

Esta fórmula obtiene la fecha actual con la función HOY, luego obtiene el primer día del mes actual utilizando la función FIN.MES. Reemplace HOY () con cualquier fecha dada para construir un calendario en un mes diferente. Más detalles sobre cómo funciona FIN.MES aquí.

Pasos para crear

1. Ocultar línea de cuadricula (optional)
2. Agregar un borde a B5:H11 (7R x 7C)
3. Nombrar K5 "start" e ingrese una fecha, por ejemplo "September 1, 2018"
4. Formula en B4 =start
5. Formato en B4 as "mmmm yyyy"
6. Seleccionar B4:H4, establecer alineamiento a "Centrar a través de selección"
7. En el rango B5:H5, ingrese abreviaciones de días (DLMMJVS)
8. Formula in B6 =start-ELEGIR(DIASEM(start),0,1,2,3,4,5,6)
9. Seleccionar B6:H11, aplicar formato numérico personalizado "d"
10. Formula en C6 =SI(B6<>"",B6,$H5)+1
11. Copiar formula in C6 a las celdas restante en la cuadrícula del calendario
12. Agregar regla de formato condicional a Anterior/Siguiente  (ver formula anterior)
13. Agregar regla de formato condicional actual (ver fórmula anterior)
14. Cambie la fecha en K5 a otra fecha de "primer mes" para probar
15. Para calendario perpetuo, fórmula en K5 = FIN.MES(HOY (), - 1) +1