Plantilla de calendario mensual del año seleccionado en hoja de Excel que nos puede servir de ayuda o referencia para calcular ciertos datos automáticamente como pueden ser la cantidad de días de la semana (de lunes a domingos) en cierto mes, año o rango de fechas.
Para cambiar el mes y el año en calendario lo podemos hacer introduciendo el mes y el año directamente en las celdas donde pone año o mes. para el año y G3-H3 para el mes.
También usando las flechas Formato Control que se encuentra entre las celdas G2-H2 para el año y G3-H3 para el mes a la izquierda disminuye y a la derecha aumentan tanto para los años como para los meses.
Si deseamos calcular si un mes tiene 5 viernes, sábados y domingos de la fecha que se encuentra en la celda B1 de la hoja donde se encuentra en la hoja calendario (esta fecha debe estar en el día 1 de mes o año que deseemos).
Es una fórmula condicionada que se encuentra en la celda L3, cuenta los días de la semana que coinciden en la fórmula con 6;7;1 (viernes, sábado y domingo respectivamente) y los suma y en el caso de ser igual a 15 nos devuelve VERDADERO o FALSO si la suma no es igual a 15.
=SI(SUMA(ENTERO((DIASEM($B$1-{1;6;7})+FIN.MES($B$1;0)-$B$1)/7))=15;VERDADERO;FALSO)
También podemos usar la siguiente fórmula en la cual tiene que coincidir 3 condiciones las 3 condiciones que sea igual a 5 para viernes, sábado y domingo (celda J3 del calendario)
=SI(Y(Y(TRUNCAR((FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)+2)+8)/7)=5;TRUNCAR((FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)+2)+8)/7)=5;TRUNCAR((FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)-0)+8)/7)=5));» VERDADERO»;» FALSO»)
La siguiente fórmula nos devuelve el mismo resultado que las 2 anteriores, en esta fórmula no hace falta como en las anteriores que la fecha que se encuentra en la celda B1 sea día 1, ya que cuenta desde el día 1 del mes que se encuentra en la celda B1 hasta final de mes (celda k3 del calendario).
=SI(Y(Y(TRUNCAR((FIN.MES($B$1;0)-FECHA(AÑO(B1);MES($B$1);1)-DIASEM(FIN.MES($B$1;0)+2)+8)/7)=5;TRUNCAR((FIN.MES($B$1;0)-FECHA(AÑO(B1);MES(B1);1)-DIASEM(FIN.MES($B$1;0)+2)+8)/7)=5;TRUNCAR((FIN.MES($B$1;0)-FECHA(AÑO(B1);MES(B1);1)-DIASEM(FIN.MES($B$1;0)-0)+8)/7)=5));» VERDADERO»;”FALSO”)
Para que un mes tenga 5 viernes, sábados y domingos (el mes tiene que tener 31 días y empezar en viernes), lo máximo que pueden coincidir en un año es 2 meses. Por ejemplo, año 2021 tiene el mes de enero y octubre.
En el mes de febrero por ejemplo sólo se repite un día de la semana 5 veces en los años bisiestos (por tener 29 días, los meses con 28 días se repiten 4 veces (4*7=28)
Las fórmulas que a continuación se indica se encuentran en la plantilla calendario, las fórmulas se encuentran en la columna B hasta la H y cuentan los días de la semana que se repiten de lunes a domingo según corresponda en cada fórmula y en las filas que se indican a continuación:
FILA 16 de la plantilla: La fórmula de esta fila cuenta los días de la semana que se repiten de lunes hasta domingo desde la fecha de la celda B1 hasta final de mes.
Lunes: =TRUNCAR((FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)+6)+8)/7)
Martes: =TRUNCAR((FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)+5)+8)/7)
Miércoles: =TRUNCAR((FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)+4)+8)/7)
Jueves: =TRUNCAR((FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)+3)+8)/7)
Viernes: ==TRUNCAR((FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)+2)+8)/7)
Sábado: =TRUNCAR((FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)+1)+8)/7)
Domingo: = TRUNCAR((FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)+0)+8)/7)
FILA 17 de la plantilla: La fórmula siguiente devuelve el mismo resultado que la fila 16.
Lunes: =ENTERO((FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)-1)+8)/7)
Martes: =ENTERO(MIN(FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)-2)+8)/7)
Miércoles: =ENTERO(MIN(FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)-3)+8)/7)
Jueves: =ENTERO(MIN(FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)-4)+8)/7)
Viernes: =ENTERO(MIN(FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)-5)+8)/7)
Sábado: =ENTERO(MIN(FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)-6)+8)/7)
Domingo: =ENTERO(MIN(FIN.MES($B$1;0)-$B$1-DIASEM(FIN.MES($B$1;0)-0)+8)/7)
FILA 20 de la plantilla: La fórmula devuelve la cantidad de días de la semana (de lunes a domingo) que se encuentran entre la fecha inicial que hay en la celda B1 y la fecha final que hay en C1.
Lunes =ENTERO(($C$1+5)/7)-ENTERO(($B$1+5)/7)+SI(DIASEM($B$1)=2;1)
Martes: =ENTERO((C1+4)/7)-ENTERO((B1+4)/7)+SI(DIASEM(B1)=3;1)
Miércoles: =ENTERO((C1+3)/7)-ENTERO((B1+3)/7)+SI(DIASEM(B1)=4;1)
Jueves: =ENTERO((C1+2)/7)-ENTERO((B1+2)/7)+SI(DIASEM(B1)=5;1)
Viernes: =ENTERO((C1+1)/7)-ENTERO((B1+1)/7)+SI(DIASEM(B1)=6;1)
Sábado: =ENTERO((C1+0)/7)-ENTERO((B1+0)/7)+SI(DIASEM(B1)=7;1)
Domingo: =ENTERO((C1-1)/7)-ENTERO((B1-1)/7)+SI(DIASEM(B1)=1;1)
FILA 21 de la plantilla: La siguiente fórmula es matricial, por ello, debemos seleccionar la fórmula en la celda y pulsar al mismo tiempo las teclas CONTROL+SHIFT+INTRO, devuelve los mismos datos que la fórmula de la fila anterior fila 20.
Lunes: {=SUMA(SI(DIASEM($B$1-1+FILA(INDIRECTO(«1:»&TRUNCAR($C$1-$B$1)+1));1)=2;1;0))}
Martes: {=SUMA(SI(DIASEM($B$1-1+FILA(INDIRECTO(«1:»&TRUNCAR($C$1-$B$1)+2));1)=2;1;0))}
Miércoles: {=SUMA(SI(DIASEM($B$1-1+FILA(INDIRECTO(«1:»&TRUNCAR($C$1-$B$1)+1));3)=2;1;0))}
Jueves: {=SUMA(SI(DIASEM($B$1-1+FILA(INDIRECTO(«1:»&TRUNCAR($C$1-$B$1)+1));2)=4;1;0))}
Viernes: {=SUMA(SI(DIASEM($B$1-1+FILA(INDIRECTO(«1:»&TRUNCAR($C$1-$B$1)+1));2)=5;1;0))}
Sábado: {=SUMA(SI(DIASEM($B$1-1+FILA(INDIRECTO(«1:»&TRUNCAR($C$1-$B$1)+1));2)=6;1;0))}
Domingo: {=SUMA(SI(DIASEM($B$1-1+FILA(INDIRECTO(«1:»&TRUNCAR($C$1-$B$1)+1));2)=7;1;0))}
FILA 22 de la plantilla: La fórmula siguiente devuelve el mismo resultado que las 2 anteriores (fila 21 y 21).
Lunes: =ENTERO(($C$1-DIASEM($C$1-1)-$B$1+8)/7)
Martes: =ENTERO(($C$1-DIASEM($C$1-2)-$B$1+8)/7)
Miércoles: =ENTERO(($C$1-DIASEM($C$1-3)-$B$1+8)/7)
Jueves: =ENTERO(($C$1-DIASEM($C$1-4)-$B$1+8)/7)
Viernes: =ENTERO(($C$1-DIASEM($C$1-5)-$B$1+8)/7)
Sábado: =ENTERO(($C$1-DIASEM($C$1-6)-$B$1+8)/7)
Domingo: =ENTERO(($C$1-DIASEM($C$1)-$B$1+8)/7)
FILA 23 de la plantilla: La fórmula siguiente devuelve el mismo resultado que las 3 anteriores (fila 20, 21 y 22), debemos tener en cuenta que funciona como se puede comprobar en la plantilla desde el año 1900 hasta 1979, por encima o debajo de ese año da error.
Lunes: =SUMAPRODUCTO(–(DIASEM(FILA(INDIRECTO($B$1&»:»&$C$1));2)=1))
Martes: =SUMAPRODUCTO(–(DIASEM(FILA(INDIRECTO($B$1&»:»&$C$1));2)=2))
Miércoles =SUMAPRODUCTO(–(DIASEM(FILA(INDIRECTO($B$1&»:»&$C$1));2)=3))
Jueves: =SUMAPRODUCTO(–(DIASEM(FILA(INDIRECTO($B$1&»:»&$C$1));2)=4))
Viernes: = =SUMAPRODUCTO(–(DIASEM(FILA(INDIRECTO($B$1&»:»&$C$1));2)=5))
Sábado: =SUMAPRODUCTO(–(DIASEM(FILA(INDIRECTO($B$1&»:»&$C$1));2)=6))
Domingo. =SUMAPRODUCTO(–(DIASEM(FILA(INDIRECTO($B$1&»:»&$C$1));2)=7))
FILA 24 de la plantilla: La siguiente fórmula le pasa como la anterior funciona en la plantilla como la anterior entre los años 1900 y 1979.
Lunes: =SUMAPRODUCTO(–(DIASEM(FILA(INDIRECTO($B$1&»:»&$C$1)))=2))
Martes: =SUMAPRODUCTO(–(DIASEM(FILA(INDIRECTO($B$1&»:»&$C$1)))=3))
Miércoles: =SUMAPRODUCTO(–(DIASEM(FILA(INDIRECTO($B$1&»:»&$C$1)))=4))
Jueves: =SUMAPRODUCTO(–(DIASEM(FILA(INDIRECTO($B$1&»:»&$C$1)))=5))
Viernes: =SUMAPRODUCTO(–(DIASEM(FILA(INDIRECTO($B$1&»:»&$C$1)))=6))
Sábado: =SUMAPRODUCTO(–(DIASEM(FILA(INDIRECTO($B$1&»:»&$C$1)))=7))
Domingo: =SUMAPRODUCTO(–(DIASEM(FILA(INDIRECTO($B$1&»:»&$C$1)))=1))
FILA 25 de la plantilla: La fórmula cuenta los días que hay entre las fechas que hay en B1 (fecha inicio) y fecha final en C1.
Lunes: =SUMA(ENTERO((DIASEM($B$1+7-{2})+$C$1-$B$1)/7))
Martes: = SUMA(ENTERO((DIASEM($B$1+7-{3})+$C$1-$B$1)/7))
Miércoles: = SUMA(ENTERO((DIASEM($B$1+7-{4})+$C$1-$B$1)/7))
Jueves: = SUMA(ENTERO((DIASEM($B$1+7-{5})+$C$1-$B$1)/7))
Viernes: = SUMA(ENTERO((DIASEM($B$1+7-{6})+$C$1-$B$1)/7))
Sábado: = SUMA(ENTERO((DIASEM($B$1+7-{7})+$C$1-$B$1)/7))
Domingo: =SUMA(ENTERO((DIASEM($B$1+7-{1})+$C$1-$B$1)/7))
FILA 26 de la plantilla: La fórmula cuenta los días de la semana que se repite entre la fecha inicial que se encuentra en la celda B1 y la fecha final que se encuentra en la C1
Lunes: =TRUNCAR(($C$1 – $B$1 – DIASEM($C$1 -1) + 8) / 7)
Martes: =TRUNCAR(($C$1 – $B$1 – DIASEM($C$1 -2) + 8) / 7)
Miércoles: =TRUNCAR(($C$1 – $B$1 – DIASEM($C$1 -3) + 8) / 7)
Jueves: =TRUNCAR(($C$1 – $B$1 – DIASEM($C$1 -4) + 8) / 7)
Viernes: =TRUNCAR(($C$1 – $B$1 – DIASEM($C$1 -5) + 8) / 7)
Sábado: =TRUNCAR(($C$1 – $B$1 – DIASEM($C$1 -6) + 8) / 7)
Domingo: =TRUNCAR(($C$1 – $B$1 – DIASEM($C$1 -7) + 8) / 7)
FILA 28 de la plantilla: La fórmula cuenta la cantidad de días de la semana que se repiten desde la fecha que se encuentra en la celda B1 hasta el 31 de diciembre del mismo año de la fecha B1.
Lunes:
= TRUNCAR((FECHA(AÑO($B$1);12;31)-$B$1 – DIASEM(FECHA(AÑO($B$1);12;31)-1) +8) / 7)
Martes: =TRUNCAR((FECHA(AÑO($B$1);12;31)-$B$1 – DIASEM(FECHA(AÑO($B$1);12;31)-2) +8) / 7)
Miércoles: =TRUNCAR((FECHA(AÑO($B$1);12;31)-$B$1 – DIASEM(FECHA(AÑO($B$1);12;31)-3) +8) / 7)
Jueves =TRUNCAR((FECHA(AÑO($B$1);12;31)-$B$1 – DIASEM(FECHA(AÑO($B$1);12;31)-4) +8) / 7)
Viernes: =TRUNCAR((FECHA(AÑO($B$1);12;31)-$B$1 – DIASEM(FECHA(AÑO($B$1);12;31)-5) +8) / 7)
Sábado: =TRUNCAR((FECHA(AÑO($B$1);12;31)-$B$1 – DIASEM(FECHA(AÑO($B$1);12;31)-6) +8) / 7)
Domingo: =TRUNCAR((FECHA(AÑO($B$1);12;31)-$B$1 – DIASEM(FECHA(AÑO($B$1);12;31)-7) +8) / 7))
Imagen de la plantilla calendario clic para ampliar, que puede descargar en el enlace que se muestra a continuación de la misma. La hoja está protegida para que no se borren las fórmulas, la contraseña está en blanco.
