Calendario con fiestas

En la última entrada vimos cómo crear un calendario y, en otra anterior, cómo hallar la fecha del domingo de Pascua. Juntando los dos ejercicios, podemos crear un calendario con fiestas que incluya las fechas más significativas del santoral.

Algunas fiestas cristianas tienen fecha fija y otras dependen de la Pascua de Resurrección. Buscando en Internet se puede obtener información para calcular las fechas de las principales fiestas religiosos y civiles.

Festividades cristianas variables	Fecha
Lunes de Carnaval	Dos días antes del comienzo de la Cuaresma.
Miércoles de Ceniza. Empieza la Cuaresma	Comprende los cuarenta días anteriores a la Semana Santa. Es el tiempo que dedican los cristianos a prepararse para celebrar los acontecimientos de la pasión, muerte y resurrección de Jesús. Da comienzo el Miércoles de Ceniza. El color litúrgico es el morado.
Domingo de Carnaval	Cuatro días después del comienzo de la Cuaresma.
Domingo de Ramos. Semana Santa.	Es la semana previa al día de Pascua, dedicada a conmemorar la pasión y muerte de Jesús (Mc. 14-15). Comienza el Domingo de Ramos (Mc. 11, 1-11) y concluye con la celebración de la vigilia Pascual, en la noche del sábado santo.
Jueves Santo	Tres días antes de la Pascua de Resurrección.
Viernes Santo	Dos días antes de la Pascua de Resurrección.
Pascua de Resurrección	Es la fiesta cristiana más importante. Se celebra la Resurrección de Jesús, ocurrida el domingo después del 14 de nisán (Jn.20, 1). El color litúrgico es el blanco.
Lunes de Pascua	Siguiente día  de la Pascua de Resurrección
Ascensión del Señor	Originariamente, 40 días después de Pascua. Esta solemnidad ha sido trasferida al 7º domingo después de Pascua.
Pentecostés	Descenso del Espíritu Santo y el inicio de la actividad de la Iglesia. Se celebra 50 días después de Pascua
Santísima Trinidad	Domingo siguiente a Pentecostés
Corpus Christi	Jueves siguiente a la Santísima Trinidad

El libro tendrá tres hojas. En la hoja Calendario crearemos el calendario. La hoja Pascua contendrá el ejercicio del cálculo de la fecha de la Pascua de Resurrección estudiado en una entrada anterior. Finalmente, la hoja Festivos contendrá la lista de los días festivos, civiles y religiosos, de todo el año y el santo del día.

Con la celda F2 de la hoja Festivos seleccionada, en el Cuadro de nombres escribimos: año_elegido

Análogamente, a la celda G2 de la misma hoja le ponemos de nombre: mes_elegido

Para seleccionar el año y el mes, colocaremos sendas barras de desplazamiento en las celdas I2 y C2 de la hoja Calendario. Para ello, en el grupo Controles de la pestaña Programador, abrimos la lista de opciones del icono Insertar y elegimos Barra de desplazamiento (control ActiveX).

Manteniendo pulsada la tecla Alt, trazamos un rectángulo que ocupe la celda I2. Una vez que el control esté incrustado, en el menú emergente, seleccionamos Propiedades. Se abrirá la ventana de propiedades. Aquí hay que cambiar algunos parámetros.

• LinkedCell: año_elegido
• Max: 2200
• Min: 1910
• SmallChange: 1
• LargeChange: 10

Cerramos la ventana de propiedades y salimos del modo de diseño haciendo clic en Modo Diseño.

Volvemos a poner otra barra de desplazamiento en C2 con estas propiedades:

• LinkedCell: mes_elegido
• Max: 12
• Min: 1
• SmallChange: 1
• LargeChange: 1

Vamos a la hoja Calendario para poner el mes y el año en la cabecera del calendario, pero, antes, pulsando Ctrl +1 para abrir el cuadro de diálogo Formato de celdas, le ponemos a la celda C3 el siguiente formato personalizado: mmmm

En C3:
=FECHA(año_elegido;mes_elegido;1)

En I3:
=año_elegido

Con las barras de desplazamiento, elegimos un mes y un año. Si seleccionamos el mes de abril de 2012 y ponemos letra Arial + blanca + 16 ppp, con justificación central, el encabezado quedará así:

Ahora, sólo tenemos que copiar las fórmulas del calendario que hicimos en una entrada anterior, adaptándolas al nuevo formato.

En C5:
=(DIASEM($C$3;2)=1)*$C$3

En D5:
=(DIASEM($C$3;2)=COLUMNA()-2)*$C$3+(C5>0)+C5

Copiamos la fórmula de D5 hacia la derecha hasta I5.

En C7:
=I5+1

En D7:
=C7+1

Copiamos la fórmula de D7 hacia la derecha hasta I7.

En C9:
=I7+1

En D9:
=C9+1

Copiamos la fórmula de D9 hacia la derecha hasta I9.

En C11:
=I9+1

En D11:
=C11+1

Copiamos la fórmula de D11 hacia la derecha hasta I11.

En C13:
=(MES(C$11+7)=MES($C$3))*(C$11+7)

Copiamos la fórmula de C13 hacia la derecha hasta I13.

En C15:
=(MES(C$11+14)=MES($C$3))*(C$11+14)

Copiamos la fórmula de C15 hacia la derecha hasta I15.

En la hoja Pascua escribimos:

En B4:
=año_elegido

En C4:
=BUSCARV(B4;$M$4:$P$9;3;VERDADERO)

En D4:
=BUSCARV(B4;$M$4:$P$9;4;VERDADERO)

En E4:
=RESIDUO(B4;19)

En F4:
=RESIDUO(B4;4)

En G4:
=RESIDUO(B4;7)

En H4:
=RESIDUO(19*E4+C4;30)

En I4:
=RESIDUO(2*F4+4*G4+6*H4+D4;7)

En J4:
=H4+I4

En K4:
=SI(J4<10;FECHA(B4;3;J4+22);SI(J4-9=26;FECHA(B4;4;19);SI(Y(J4-9=25;H4=28;I4=6;E4>10);FECHA(B4;4;18);FECHA(B4;4;J4-9))))

En la hoja Festivos hemos agrupado las festividades en tres bloques: el primero, contiene las festividades oficiales; el segundo, las festividades que dependen de la Pascua de Resurrección; el tercero, contiene las festividades religiosas fijas. Algunos días pueden coincidir en dos bloques, pero al colocarlos en el orden mencionado, la función BUSCARV (que emplearemos más adelante) elegirá siempre la festividad que esté en el primero de los bloques. Por otra parte, en la columna D indicaremos si la fecha es laborable o es día festivo. Este dato nos servirá para resaltar en el calendario los días no laborables.

Las fórmulas de la columna D mostrarán la fecha del día festivo correspondiente al año que se ha seleccionado en F2. Todas las fórmulas de los rangos B2:B4 y B29:B395 tendrán el mismo formato. Por ejemplo, la fórmula de B2 es la siguiente: =FECHA(año_elegido;5;1)

Las fechas del bloque Festividades religiosas relacionadas dependen de la fecha de la Pascua, que está en B20 (se han incluido la Epifanía y el Bautismo de Jesús, que no dependen de la Pascua pero que están relacionadas entre sí).

En B8: =FECHA(año_elegido;1;6)
En B9: =B8+ELEGIR(DIASEM(B8;2);6;5;4;3;2;1;7)
En B10: =B12-2
En B11: =B10+1
En B12: =B20-46
En B13: =B12+1
En B14: =B13+1
En B15: =B14+1
En B16: =B15+1
En B17: =B20-7
En B18: =B20-3
En B19: =B20-2
En B20: =Pascua!K4
En B21: =B20+1
En B22: =B20+42
En B23: =B20+49
En B24: =B23+7
En B25: =B24+4

Volvemos a la hoja Calendario para poner las festividades debajo de los días. En algunos casos, el texto no cabe en una sola línea, por lo que hay que ajustar el formato de las celdas.

Seleccionamos las celdas C6:I6 y pulsamos Ctrl +1. En al ficha Alineación, marcamos Ajustar texto y nos aseguramos que esté seleccionada la posición Centrar, tanto en la alineación horizontal como en la vertical.

Copiamos este formato en las celdas de las filas 8, 10, 12, 14 y 16.

Ahora, ponemos las festividades de la siguiente manera:

En C6:
=SI.ERROR(BUSCARV(C5;Festivos!$B$1:$C$500;2;FALSO);"")

Copiamos la fórmula de C6 hacia la derecha hasta I6. Volvemos a copiar C6:I6 en las celdas inferiores de las filas 8, 10, 12, 14 y 16.

Para poner en rojo las fiestas oficiales usaremos un formato condicional.

Estando en la celda C5, accedemos a Inicio + Formato condicional + Nueva regla. Ponemos la fórmula:
=("Sí"=BUSCARV(C5;Festivos!$B$1:$D$500;3;FALSO)) y, en Formato, elegimos la fuente de color rojo.

Copiamos este formato en todas las celdas donde hay números. Para ello, estando en C5, hacemos doble clic en Copiar formato (pestaña Inicio) y pasamos la brocha sobre las celdas a las que queremos dar formato.

Nos ponemos en C6 y volvemos a poner el mismo formato condicional a esta celda. Es decir, la fórmula debe ser: =("Sí"=BUSCARV(C5;Festivos!$B$1:$D$500;3;FALSO))

De nuevo, con Copiar formato  aplicamos el formato al resto de las filas pares. De este modo, el ejercicio está terminado.

Nota: En Excel 2003 ni existe la función SI.ERROR ni se puede hacer referencia a otro libro o a otra hoja cuando se pone un formato condicional (las referencias hay que sustituirlas por "nombres" de Excel). Por ese motivo, en el archivo que se puede descargar más abajo, se han hecho las oportunas modificaciones para que el calendario funcione correctamente.

Descargar archivo (S26-Calendario con fiestas.xls)

Descargar archivo (S26-Calendario con fiestas.xlsx)