Fórmulas Excel que nos pueden servir de ayuda o referencia para saber el día corresponde al ultimo lunes, martes, miércoles, jueves, viernes, sábado o domingo del mes de la fecha que se encuentra en la celda A1, según corresponda.
En la celda A1, ponemos la fecha con el día, mes y año, y en las celdas donde vamos a poner las fórmulas tenemos que poner el formato fecha y después podemos copiar y pegar las formulas para que nos devuelva la fecha según corresponda.
La fórmula es la misma para todos los días, solo hay que cambiar el número en la formula entre 1 y el 7, 1 para domingo, 2 lunes y así hasta 7 sábado como se muestra a continuación
=FIN.MES($A$1;0) - RESIDUO(DIASEM(FIN.MES($A$1;0)) - 1; 7)
=FIN.MES($A$1;0) - RESIDUO(DIASEM(FIN.MES($A$1;0)) - 2; 7)
=FIN.MES($A$1;0) - RESIDUO(DIASEM(FIN.MES($A$1;0)) - 3; 7)
=FIN.MES($A$1;0) - RESIDUO(DIASEM(FIN.MES($A$1;0)) - 4; 7)
=FIN.MES($A$1;0) - RESIDUO(DIASEM(FIN.MES($A$1;0)) - 5; 7)
=FIN.MES($A$1;0) - RESIDUO(DIASEM(FIN.MES($A$1;0)) - 6; 7)
=FIN.MES($A$1;0) - RESIDUO(DIASEM(FIN.MES($A$1;0)) - 7; 7)
Con las siguientes fórmulas obtendríamos el mismo resultado:
=FECHA(AÑO($A$1); MES($A$1)+1; 1) - DIASEM(FECHA(AÑO($A$1); MES($A$1)+1; 1) - 1)
=FECHA(AÑO($A$1); MES($A$1)+1; 1) - DIASEM(FECHA(AÑO($A$1); MES($A$1)+1; 1) - 2)
=FECHA(AÑO($A$1); MES($A$1)+1; 1) - DIASEM(FECHA(AÑO($A$1); MES($A$1)+1; 1) - 3)
=FECHA(AÑO($A$1); MES($A$1)+1; 1) - DIASEM(FECHA(AÑO($A$1); MES($A$1)+1; 1) - 4)
=FECHA(AÑO($A$1); MES($A$1)+1; 1) - DIASEM(FECHA(AÑO($A$1); MES($A$1)+1; 1) - 5)
=FECHA(AÑO($A$1); MES($A$1)+1; 1) - DIASEM(FECHA(AÑO($A$1); MES($A$1)+1; 1) - 6)
=FECHA(AÑO($A$1); MES($A$1)+1; 1) - DIASEM(FECHA(AÑO($A$1); MES($A$1)+1; 1) - 7)
La fórmulas que se muestran a continuación tienen la condición para que nos devuelve la fecha del 5º (lunes, martes hasta domingo) del mes de la fecha A1, en el caso que ese mes no tenga 5 días de la semana iguales, no devuelve la frase que no hay 5º lunes, martes o el que corresponda.
Dentro de la fórmula contiene la función RESIDUO en la 2º es donde ponemos el número correspondiente en este caso 1 para lunes hasta 7 para domingo, como se indica a continuación:
Ejemplo: fórmula para lunes
=SI(MES(FECHA(AÑO($A$1); MES($A$1); 1) + RESIDUO(1 - DIASEM(FECHA(AÑO($A$1); MES($A$1); 1); 2) + 7; 7) + 28)=MES($A$1); FECHA(AÑO($A$1); MES($A$1); 1) + RESIDUO(1 - DIASEM(FECHA(AÑO($A$1); MES($A$1); 1); 2) + 7; 7) + 28; "No hay 5º lunes")
Fórmula para martes
=SI(MES(FECHA(AÑO($A$1); MES($A$1); 1) + RESIDUO(2 - DIASEM(FECHA(AÑO($A$1); MES($A$1); 1); 2) + 7; 7) + 28)=MES($A$1); FECHA(AÑO($A$1); MES($A$1); 1) + RESIDUO(2 - DIASEM(FECHA(AÑO($A$1); MES($A$1); 1); 2) + 7; 7) + 28; "No hay 5º martes")
Fórmula para miércoles
=SI(MES(FECHA(AÑO($A$1); MES($A$1); 1) + RESIDUO(3 - DIASEM(FECHA(AÑO($A$1); MES($A$1); 1); 2) + 7; 7) + 28)=MES($A$1); FECHA(AÑO($A$1); MES($A$1); 1) + RESIDUO(3 - DIASEM(FECHA(AÑO($A$1); MES($A$1); 1); 2) + 7; 7) + 28; "No hay 5º miércoles")
Fórmula para jueves
=SI(MES(FECHA(AÑO($A$1); MES($A$1); 1) + RESIDUO(4 - DIASEM(FECHA(AÑO($A$1); MES($A$1); 1); 2) + 7; 7) + 28)=MES($A$1); FECHA(AÑO($A$1); MES($A$1); 1) + RESIDUO(4 - DIASEM(FECHA(AÑO($A$1); MES($A$1); 1); 2) + 7; 7) + 28; "No hay 5º jueves")
Fórmula para viernes
=SI(MES(FECHA(AÑO($A$1); MES($A$1); 1) + RESIDUO(5 - DIASEM(FECHA(AÑO($A$1); MES($A$1); 1); 2) + 7; 7) + 28)=MES($A$1); FECHA(AÑO($A$1); MES($A$1); 1) + RESIDUO(5 - DIASEM(FECHA(AÑO($A$1); MES($A$1); 1); 2) + 7; 7) + 28; "No hay 5º viernes")
Fórmula para sábado
=SI(MES(FECHA(AÑO($A$1); MES($A$1); 1) + RESIDUO(6 - DIASEM(FECHA(AÑO($A$1); MES($A$1); 1); 2) + 7; 7) + 28)=MES($A$1); FECHA(AÑO($A$1); MES($A$1); 1) + RESIDUO(6 - DIASEM(FECHA(AÑO($A$1); MES($A$1); 1); 2) + 7; 7) + 28; "No hay 5º sábado")
Fórmula para domingo
=SI(MES(FECHA(AÑO($A$1); MES($A$1); 1) + RESIDUO(7 - DIASEM(FECHA(AÑO($A$1); MES($A$1); 1); 2) + 7; 7) + 28)=MES($A$1); FECHA(AÑO($A$1); MES($A$1); 1) + RESIDUO(7 - DIASEM(FECHA(AÑO($A$1); MES($A$1); 1); 2) + 7; 7) + 28; "No hay 5º domingo")