Resumen
Puede configurar una cuadrícula de calendario dinámica 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-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
donde "inicio" es el rango con nombre K5 y contiene la fecha 1 de septiembre de 2018.
Explicación
Nota: Este ejemplo asume 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 principal problema es calcular la fecha en la primera celda del calendario (B6). Esto se hace con esta fórmula:
=start-CHOOSE(WEEKDAY(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, porque permite valores arbitrarios para cada día de la semana. Usamos esta función para revertir cero días cuando el primer día del mes es 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:
=IF(B6"",B6,$H5)+1
Esta fórmula prueba la celda inmediatamente a la izquierda en busca de un valor. Si no se encuentra ningún valor, extrae un valor de la columna H en la fila anterior. Tenga en cuenta que $ H5 es una referencia mixta, para bloquear la columna cuando la fórmula se copia en toda la cuadrícula. Se usa la misma fórmula en todas las celdas excepto B6.
Reglas de formato condicional
El calendario utiliza fórmulas de formato condicional para cambiar el formato para sombrear los meses anteriores y futuros y resaltar el día actual. Ambas reglas se aplican a toda la cuadrícula. Para los meses anteriores y siguientes, la fórmula es:
=MONTH(B6)MONTH(start)
Para el día actual, la fórmula es:
=B6=TODAY()
Para obtener más detalles, consulte: Formato condicional con fórmulas (10 ejemplos)
Encabezado 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 en 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:
=EOMONTH(TODAY(),-1)+1
Esta fórmula obtiene la fecha actual con la función HOY, luego obtiene el primer día del mes actual usando la función EOMONTH. Reemplace TODAY () con una fecha determinada para crear un calendario en un mes diferente. Más detalles sobre cómo funciona EOMONTH aquí.
Pasos para crear
- Ocultar líneas de cuadrícula (opcional)
- Agregue un borde a B5: H11 (7R x 7C)
- Nombre K5 "inicio" e ingrese una fecha como "1 de septiembre de 2018"
- Fórmula en B4 = inicio
- Formato B4 como "mmmm aaaa"
- Seleccione B4: H4, establezca la alineación en "Centrar en la selección"
- En el rango B5: H5, ingrese las abreviaturas de los días (SMTWTFS)
- Fórmula en B6 = start-CHOOSE (WEEKDAY (inicio), 0,1,2,3,4,5,6)
- Seleccione B6: H11, aplique el formato de número personalizado "d"
- Fórmula en C6 = IF (B6 "", B6, $ H5) +1
- Copie la fórmula en C6 en las celdas restantes en la cuadrícula del calendario
- Agregar regla de formato condicional anterior / siguiente (consulte la fórmula anterior)
- Agregar la regla de formato condicional actual (consulte la fórmula anterior)
- Cambie la fecha en K5 a otra fecha del "primer mes" para probar
- Para el calendario perpetuo, fórmula en K5 = EOMONTH (HOY (), - 1) +1
