Fórmulas Excel: a continuación se mostraran varias fórmulas para localizar el día de la semana del último lunes, Martes, Miércoles; Jueves, Viernes, Sábado o domingo de la fecha que se encuentra en la celda A1. Debemos tener en cuenta que en esta fórmula, podemos cambiar el AÑO y el MES, pero el día debe ser el 1, por ejemplo podemos poner 1/5/2015 u otra fecha que deseemos pero sin cambiar el día 1 para las fórmulas siguientes:
Para el último lunes del mes
=SI(DIA(FIN.MES(A1;0))=28;A1-DIASEM(A1-2)+DIA(28);SI(DIA(FIN.MES(A1;0))=29;A1-DIASEM(A1-1)+DIA(29);SI(DIA(FIN.MES(A1;0))=30;A1-DIASEM(A1-0)+DIA(30);SI(DIA(FIN.MES(A1;0))=31;A1-DIASEM(A1-6)+DIA(31)))))
Martes:
=SI(DIA(FIN.MES(A1;0))=28;A1-DIASEM(A1-3)+DIA(28);SI(DIA(FIN.MES(A1;0))=29;A1-DIASEM(A1-2)+DIA(29);SI(DIA(FIN.MES(A1;0))=30;A1-DIASEM(A1-1)+DIA(30);SI(DIA(FIN.MES(A1;0))=31;A1-DIASEM(A1-7)+DIA(31)))))
Miércoles:
=SI(DIA(FIN.MES(A1;0))=28;A1-DIASEM(A1-4)+DIA(28);SI(DIA(FIN.MES(A1;0))=29;A1-DIASEM(A1-3)+DIA(29);SI(DIA(FIN.MES(A1;0))=30;A1-DIASEM(A1-2)+DIA(30);SI(DIA(FIN.MES(A1;0))=31;A1-DIASEM(A1-8)+DIA(31)))))
Jueves
=SI(DIA(FIN.MES(A1;0))=28;A1-DIASEM(A1-5)+DIA(28);SI(DIA(FIN.MES(A1;0))=29;A1-DIASEM(A1-4)+DIA(29);SI(DIA(FIN.MES(A1;0))=30;A1-DIASEM(A1-3)+DIA(30);SI(DIA(FIN.MES(A1;0))=31;A1-DIASEM(A1-9)+DIA(31)))))
Viernes
=SI(DIA(FIN.MES(A1;0))=28;A1-DIASEM(A1-6)+DIA(28);SI(DIA(FIN.MES(A1;0))=29;A1-DIASEM(A1-5)+DIA(29);SI(DIA(FIN.MES(A1;0))=30;A1-DIASEM(A1-4)+DIA(30);SI(DIA(FIN.MES(A1;0))=31;A1-DIASEM(A1-10)+DIA(31)))))
Sábado
=SI(DIA(FIN.MES(A1;0))=28;A1-DIASEM(A1-7)+DIA(28);SI(DIA(FIN.MES(A1;0))=29;A1-DIASEM(A1-6)+DIA(29);SI(DIA(FIN.MES(A1;0))=30;A1-DIASEM(A1-5)+DIA(30);SI(DIA(FIN.MES(A1;0))=31;A1-DIASEM(A1-11)+DIA(31)))))
Domingo
=SI(DIA(FIN.MES(A1;0))=28;A1-DIASEM(A1-8)+DIA(28);SI(DIA(FIN.MES(A1;0))=29;A1-DIASEM(A1-7)+DIA(29);SI(DIA(FIN.MES(A1;0))=30;A1-DIASEM(A1-6)+DIA(30);SI(DIA(FIN.MES(A1;0))=31;A1-DIASEM(A1-12)+DIA(31)))))
La siguiente fórmula devolvería el primer día de la semana después de la fecha que se encuentra en la celda (A1) para saber el primer lunes debemos siempre poner el día 1 del mes y el año que deseemos, ejemplo:
Lunes =A1-DIASEM(A1-2)+7
Martes =A1-DIASEM(A1-3)+7
Miércoles =A1-DIASEM(A1-4)+7
Jueves =A1-DIASEM(A1-5)+7
Viernes =A1-DIASEM(A1-6)+7
Sábado =A1-DIASEM(A1-7)+7
Domingo =A1-DIASEM(A1-8)+7
A las fórmulas anteriores si deseamos saber cuando es el 2º dia de la semana en vez de +7 ponemos +14 para el 3º 21, 4º 28, por ejemplo para el 2º, 3º y 4º lunes:
=$A$1-DIASEM($A$1-2)+14 (para el 2º lunes siguiente a la fecha de la celda A1)
=$A$1-DIASEM($A$1-2)+21 (para el 3º lunes siguiente a la fecha de la celda A1)
=$A$1-DIASEM($A$1-2)+28 (para el 4º lunes siguiente a la fecha de la celda A1)
Si queremos saber si hay un 5º día en el mes (en la fecha debemos aplicar como día el 1 y el mes y año el que deseemos).
5º Lunes
=SI(TRUNCAR((FIN.MES($A$1;0)-$A$1-DIASEM(FIN.MES($A$1;0)+6)+8)/7)=5;$A$1-DIASEM($A$1-2)+35;»No hay 5ª lunes»)
5º Martes
=SI(TRUNCAR((FIN.MES($A$1;0)-$A$1-DIASEM(FIN.MES($A$1;0)-2)+8)/7)=5;$A$1-DIASEM($A$1-3)+35;»No hay 5º martes»)
5º Miércoles
=SI(TRUNCAR((FIN.MES($A$1;0)-$A$1-DIASEM(FIN.MES($A$1;0)-3)+8)/7)=5;$A$1-DIASEM($A$1-4)+35;»No hay 5º miércoles»)
5º Jueves
=SI(TRUNCAR((FIN.MES($A$1;0)-$A$1-DIASEM(FIN.MES($A$1;0)-4)+8)/7)=5;$A$1-DIASEM($A$1-5)+35;»No hay 5º jueves»)
5º Viernes
=SI(TRUNCAR((FIN.MES($A$1;0)-$A$1-DIASEM(FIN.MES($A$1;0)-5)+8)/7)=5;$A$1-DIASEM($A$1-6)+35;»No hay 5º viernes»)
5º Sábado
=SI(TRUNCAR((FIN.MES($A$1;0)-$A$1-DIASEM(FIN.MES($A$1;0)-6)+8)/7)=5;$A$1-DIASEM($A$1-7)+35;»No hay 5º Sábado»)
Domingo
=SI(TRUNCAR((FIN.MES($A$1;0)-$A$1-DIASEM(FIN.MES($A$1;0)-7)+8)/7)=5;$A$1-DIASEM($A$1-8)+35;»No hay 5º Domingo»)
Aplicadas las correspondientes fórmulas en la hoja de excel quedaría como se muestra en la siguiente imagen (clic para ampliar). Esta hoja se puede descargar en el enlace que se encuentra debajo de ésta, donde podá comprobar el funcionamiento de las mismas . Así como otras fórmulas para saber cuantos días de la semana, lunes, martes, miércoles, jueves, viernes, sabados y domingos hay dentro del mismo mes.
Clic para Descargar archivo Control-de-días-1
Hola Kevin, la fórmula =$A$1-DIASEM($A$1-2;2) muestra el primer martes anterior a la fecha que se encuentra en A1, si coincide en martes muestra el martes de la fecha anterior, también la siguiente fórmula devolvería el mismo resultado: =$A$1-DIASEM($A$1-3)
por lo tanto si ponemos =$A$1-DIASEM($A$1-2;2)-14 primero localiza el primer martes anterior a esa fecha y después resta 14 días.
Si deseas retroceder 14 días a la fecha inicial puedes aplicar la siguiente fórmula
Si deseas restar 14 días a la fecha que se encuentra en la celda A1, puedes aplicar la siguiente fórmula: =FECHA(AÑO(A1);MES(A1);DIA(A1)-14)
Si es del final de mes de la fecha que se encuentra en A1 =FIN.MES(A1; 0)-14
saludos
Buen dia aplicando la formula =$A$1-DIASEM($A$1-2;2)-14 me permite retroseder 14 dias y redondear al dia martes una fecha. Pero existe un problema cuando la fecha que uso de base es un martes ya que me retrocede 3 semanas y no 2 semanas como lo requiero. Como puedo solucionar esto??
Buen dia aplicando la formula =$A$1-DIASEM($A$1-2;2)-14 me permite retroseder 14 dias y redondear al dia martes una fecha. Pero existe un problema cuando la fecha que uso de base es un martes ya que me retrocede 3 semanas y no 2 semanas como lo requiero. Como puedo solucionar esto?
Hola Ariel
Podemos usar la función FECHA que devuelve el número de serie secuencial que representa una fecha determinada.
Sintaxis: FECHA(año; mes; día)
Tiene los siguientes argumentos, en las que hay que poner el año, el mes y el día, si por ejemplo ponemos en una celda la siguiente fórmula =Fecha(2020;5;25), nos devuelve la fecha del 5 de mayo del 2015.
Si tenemos el año en una celda por ejemplo en la celda A1 y queremos que nos devuelva la fecha del 10 de mayo aplicamos la siguiente fórmula: =FECHA(A1;5;10), de esta forma al cambiar el año en la celda A1, también cambia en la celda, si queremos que nos devuelva el 10 de junio del año que se encuentra en la celda A1, aplicamos la siguiente fórmula: =FECHA(A1;6;10)
Si deseamos que nos devuelva el 10 de junio del año actual: =FECHA(AÑO(HOY());6;10)
Como puedo obtener el dia 10 de cada mes de cada ano?
Es decir, necesito el 10 de Mayo, Luego el 10 de Junio y asi sucesivamente para el 2018, 2019, etc…Gracias!
Hola Ángel,
La fórmula lleva varias condiciones para que devuelva un resultado u otro y esto va depender si el mes tiene 28 o 29 días (si es febrero) y 30 o 31 para el resto de los meses.
Ejemplo: si deseamos saber si la fecha que hay en la celda A1 tiene 28 días aplicamos la siguiente fórmula y nos devuelve como resultado verdadero SI y en caso contrario NO.
=SI(DIA(FIN.MES(A1;0))=28;»SI»;»NO»)
Si deseamos saber si el mes de la fecha que hay en la celda A1 tiene 29 días, aplicamos la siguiente fórmula, devolviendo como valor verdadero Si y falso NO: =SI(DIA(FIN.MES(A1;0))=28;»SI»;»NO»)
Aplicando la siguiente fórmula en el caso de que tenga 28 días nos devolvería el primer lunes
=SI(DIA(FIN.MES(A1;0))=28;A1-DIASEM(A1-2)+DIA(28)) en caso contrario nos devolvería un resultado falso y buscaría la siguiente condición y así hasta que encuentre la correcta.
=SI(DIA(FIN.MES(A1;0))=28;A1-DIASEM(A1-2)+DIA(28);SI(DIA(FIN.MES(A1;0))=29;A1-DIASEM(A1-1)+DIA(29);SI(DIA(FIN.MES(A1;0))=30;A1-DIASEM(A1-0)+DIA(30);SI(DIA(FIN.MES(A1;0))=31;A1-DIASEM(A1-6)+DIA(31)))))
Aplicando la condición correspondiente si deseamos saber si la fecha que hay en la celda A1 es lunes nos devolvería la misma fecha de la celda A1, aplicando la siguiente fórmula formula: =A1-DIASEM(A1-2)+7
Si es martes: =A1-DIASEM(A2-3)+7
Para miércoles: =A1-DIASEM(A2-4)+7
Para jueves: =A1-DIASEM(A2-5)+7
Para viernes: =A1-DIASEM(A2-6)+7
Para sábado: =A1-DIASEM(A2-7)+7
Por ejemplo:
Si la fecha que hay en la celda A1 (ejemplo: 1/1/2018 corresponde a lunes) y ponemos el formato número nos muestra 43101, aplicando la siguiente fórmula fórmula =A1-DIASEM(A1-2)+7 obtendríamos la el mismo número para lunes.
Si la fecha de la celda que hay en A1 corresponde a martes aplicariamos la siguiente fórmula =A1-DIASEM(A1-2)+7 y obtendríamos el mismo resultado que la celda
Con la siguiente fórmula si la celda A1 corresponde al día 1 de cada mes aplicando la siguiente fórmula también nos devolvería la fecha de la 2º semana de ese mes. .
=SI(DIASEM(A1)=1; A1+7;SI(DIASEM(A1)=2;A1+7;SI(DIASEM(A1)=3;A1+7;SI(DIASEM(A1)=4;A1+7;SI(DIASEM(A1)=5;A1+7;SI(DIASEM(A1)=6;A1+7;A1+7))))))
Saludos.
Que tal, me parecen excelentes las fórmulas!
Solo me gustaría saber, para el caso del primer día de la semana después de la fecha indicada, cual fue el razonamiento para llegar a la fórmula. Gracias!
Hola Lourdes, aquí te dejo una fórmula por si te sirve de ayuda
Si la fecha que vence se encuentra en la celda A1 aplicas la siguiente fórmula: =A1-DIASEM(A1-6)+7
Si la fecha se encuentra en otra celda la cambiamos en la fórmula por ejemplo si está en la celda B2 se aplica aplicamos la siguiente fórmula: =B1-DIASEM(B2-6)+7. si la factura vence el viernes nos devolvería la fecha que vence, si vence otro día que no sea viernes nos devolvería el primer viernes posterior a la fecha.
Hola
necesito una formula para poner fecha de pago
ejemplo si la factura vence el lunes 27/03/2017 se debera pagar el viernes 31/03/2017 pues solo pagamos los viernes
Me ayudan con una formula
Gracias
Hola
Tengo que poner fechas de venciento de mis facturas, solo se pagan los dias viernes, si la factura vence del sabado al jueves posteriores se paga al siguiente viernes habil.
es decir si vence en lunes 27 la debo de pagar el viernes 31.
Me ayudan
Gracias
Hola Victorm,
Supongamos que tenemos una fecha en la celda A1 si aplicamos en una celda la siguiente fórmula:
=SI(A1=FIN.MES(A1;0);»SI»;»NO»)
Nos devuelve como valor verdadero Si (en el caso de que sea fin de mes)
Nos devuelve como valor falso NO (en el caso de que sea fin de mes)
Los resultados que nos devuelve los podemos cambiar por otros o aplicar la fórmula deseada en el caso de que sea verdadero o falso.
En el siguiente ejemplo si es Fin de mes la fecha de la celda A1 multiplica la celda A2 por la celda A5 y caso de que sea Falso devuelve 0.
=SI(A1=FIN.MES(A1;0);A2*A5;0)
Hola buen dia, quiero saber si existe una formula para que si en determinada celda es fin de mes, devolver una valor «SI» o valor «NO» en otra celda. lo ocupo para realizar calculo de intereses ya que si es fin de mes debe realizar capitalizacion de intereses.
Saludos cordiales
Hola Ana,
Aquí te dejo unos ejemplos por si te sirven de ayuda o referencia. Tenemos en la celda B1 el año en una celda vacía aplicamos la siguiente fórmula: =FECHA(B1;1;1)-DIASEM(FECHA(B1;1;1)-8)+7
Si queremos saber la fecha del primer domingo del año 2018 en la fórmula cambiamos donde se encuentra la celda B1 por el año (en este caso 2018)
=FECHA(2018;1;1)-DIASEM(FECHA(2018;1;1)-8)+7
Si tenemos en la celda B1 la fecha completa del primer dia del mes y año seleccionado por ejemplo 01-01-2018 aplicamos la siguiente fórmula: =B1-DIASEM(B1-8)+7
Si por ejemplo tenemos la fecha completa del día 1-1-(año que deseemos), aplicando la siguiente fórmula matricial nos devuelve el1º domingo de enero del año que indiquemos. para crear una fórmula matricial debemos pulsar al mismo tiempo las teclas Ctrl + Mayus + Entrar. La fórmula tiene que quedar cerrada entre estas llaves {}
{=K.ESIMO.MENOR(SI(DIASEM(FILA(INDIRECTO(FECHA(AÑO(A1);MES(A1);1)&»:»&FECHA(AÑO(A1);MES(A1)+1;1)-1)))=1;FILA(INDIRECTO(FECHA(AÑO(A1);MES(A1);1)&»:»&FECHA(AÑO(A1);MES(A1)+1;1)-1)));1)}
Hola y como seria para calcular el primer domingo del año
Hola Persi, Si por ejemplo la fecha se encuentra en la celda A1 (fecha completa), y quieres saber el primer viernes del mes de la fecha que se encuentra en la celda A1, te puede servir la siguiente fórmula:
Y si deseamos saber el 1º lunes del mes de la fecha que se encuentra en la celda A1
=FECHA(AÑO(A1);MES(A1);1-DIASEM(FECHA(AÑO(A1);MES(A1);1)-2)+7)Hola muy buena la publicacion y como seria para determinar el primer viernes de cada mes les agradeceria mucho