Excel Gratis

Aquí dejo algunas fórmulas que nos pueden servir de ayuda u orientación (las primeras que a continuación se indican son para saber cuántas apariciones de un día en concreto de la semana (de lunes a domingo), se encuentra en una columna, fila o rango de fechas.

En las siguientes fórmulas se aplica la función (SUMAPRODUCTO Y TEXTO), en los ejemplos que a continuación se indican las fechas se encuentran en las celdas de la columna A (rango A1:A500). Una de las condiciones es que el año sea =2020 y otra es que el día de la semana que deseamos contar (de lunes a domingo).

Debemos tener en cuenta los nombres de los días no deben ir separados por espacios y poner acento al miércoles y sábado, como se indica a continuación:

=SUMAPRODUCTO(–(AÑO($A$1:$A$500)=2020)*(TEXTO(A1:$A$500;»dddd»)=»lunes»))
=SUMAPRODUCTO(–(AÑO($A$1:$A$500)=2020)*(TEXTO($A1:A$500;»dddd»)=»martes»))
=SUMAPRODUCTO(–(AÑO($A$1:$A$500)=2020)*(TEXTO($A1:A$500;»dddd»)=»miércoles»))
=SUMAPRODUCTO(–(AÑO($A$1:$A$500)=2020)*(TEXTO($A1:A$500;»dddd»)=»jueves»))
=SUMAPRODUCTO(–(AÑO($A$1:$A$500)=2020)*(TEXTO($A1:A$500;»dddd»)=»viernes»))
=SUMAPRODUCTO(–(AÑO($A$1:$A$500)=2020)*(TEXTO($A1:A$500;»dddd»)=»sábado»))
=SUMAPRODUCTO(–(AÑO($A$1:$A$500)=2020)*(TEXTO($A1:A$500;»dddd»)=»domingo»))

Obtendríamos el mismo resultando usando la función DIASEM, en la cual el día de la semana, empleamos de argumento tipo 2, que determina el valor que devuelve (1-para lunes, 2-martes y así hasta 7-domingo), como se muestra a continuación:

lunes: =SUMAPRODUCTO(–(AÑO($A$1:$A$500)=2020)*(DIASEM($A$1:$A$500; 2)=1))
martes =SUMAPRODUCTO(–(AÑO($A$1:$A$500)=2020)*(DIASEM($A$1:$A$500; 2)=2))
miércoles: =SUMAPRODUCTO(–(AÑO($A$1:$A$500)=2020)*(DIASEM($A$1:$A$500; 2)=3))
jueves: =SUMAPRODUCTO(–(AÑO($A$1:$A$500)=2020)*(DIASEM($A$1:$A$500; 2)=4))
viernes: =SUMAPRODUCTO(–(AÑO($A$1:$A$500)=2020)*(DIASEM($A$1:$A$500; 2)=5))
sábado: =SUMAPRODUCTO(–(AÑO($A$1:$A$500)=2020)*(DIASEM($A$1:$A$500; 2)=6))
domingo: =SUMAPRODUCTO(–(AÑO($A$1:$A$500)=2020)*(DIASEM($A$1:$A$500; 2)=7))

En estas fórmulas una de las condiciones es que el año sea igual al 2020 (se puede cambiar por otro), si el año se pone en la celda M1, cambiamos en la fórmula =2020 por =$M$1, como se indica a continuación (por ejemplo para el lunes). 

=SUMAPRODUCTO(–(AÑO($A$1:$A$500)=$M$1)*(TEXTO(A1:$A$500;»dddd»)=»lunes»))
=SUMAPRODUCTO(–(AÑO($A$1:$A$500)= $M$1)*(DIASEM($A$1:$A$500; 2)=1))

En las siguientes fórmulas al no incluir la condición del año cuenta todas las fechas del rango A1:A500 que coinciden con el día de la semana que seleccionemos, como se indica a continuación:

Para lunes: =SUMAPRODUCTO(–(DIASEM($A$2:$A500;2)=1))
Martes: =SUMAPRODUCTO(–(DIASEM($A$2:$A500;2)=2))
Miércoles: =SUMAPRODUCTO(–(DIASEM($A$2:$A500;2)=3))
Jueves: =SUMAPRODUCTO(–(DIASEM($A$2:$A500;2)=4))
Viernes: =SUMAPRODUCTO(–(DIASEM($A$2:$A500;2)=5))
Sábado: =SUMAPRODUCTO((DIASEM($A$1:$A$500;2)=6)*1)-CONTAR.BLANCO($A$1:$A$500)
Domingo: =SUMAPRODUCTO(–(DIASEM($A$2:$A500;2)=7))

También podemos identificar los días de la semana que seleccionemos (de lunes a domingo) que las muestre con con el formato deseado (color del texto, relleno celda, etc.). Para ello, seleccionamos las fechas que deseamos y hacemos clic en la pestaña Inicio>> Formato condicional>>Administrar reglas >>nueva regla >> seleccionamos utilice una fórmula para determinar en qué celdas deseamos que se aplique el formato (que es donde debemos poner la fórmula, formato “color letra, relleno, etc.”, así como (celda, rango de celdas, filas o columnas), así el rango en este ejemplo A1:A500 y la fórmula, en la que empleamos la función DIASEM, sólo tenemos que cambiar el número que hace referencia al día de semana que deseamos que resalten las fechas: (=1 para lunes, =2 martes, =3-miércoles, =4-jueves, =5-viernes, =6-sábado y =7- domingo, como se indica a continuación:

Lunes: =DIASEM(A1;2)=1
martes: =DIASEM(A1;2)=2
miércoles: =DIASEM(A1;2)=3
jueves: =DIASEM(A1;2)=4
viernes: =DIASEM(A1;2)=5
sábado: =DIASEM(A1;2)=6
domingo: =DIASEM(A1;2)=7

En estas fórmulas usamos el signo igual (=), si cambiamos por el signo mayor o igual que (>=), nos muestra los días que son igual y mayores al número indicado, por ejemplo para mostrar los días que son >=5 que corresponden (el 5-viernes, 6-sábado y 7-domingo y aplicamos la siguiente fórmula:

viernes, sábado y domingo =DIASEM(A1;2)>=5
Para lunes, martes y miercoles: =DIASEM(A1;2)<=3

Podemos emplear la función (O), para aplicar más de un criterio como se indica a continuación:

para los sábados, domingos y miércoles:
=O(DIASEM(A1;2)=6;O((DIASEM(A1;2)=7);O((DIASEM(A1;2)=3)=VERDADERO)))
miércoles y domingos:  =O(DIASEM(A1;2)=3;DIASEM(A1;2)=7)
lunes y martes: =O(DIASEM(A1;2)=1;DIASEM(A1;2)=2)
lunes y martes: =O(DIASEM(A1;2)=1;O((DIASEM(A1;2)=2)=VERDADERO))

En las siguientes fórmulas se utiliza función TEXTO, teniendo en cuenta que los días de la semana (de lunes a domingo), no deben tener espacios en blanco y el miércoles y sábado deben ir acentuados.

para los miércoles
=(TEXTO(A1;»dddd»)=»miércoles»)   también    =Y(TEXTO(A1;»dddd»)=»miércoles»)
sábados y miércoles:
=O(TEXTO(A1;»dddd»)=»sábado»;TEXTO(A1;»dddd»)=»miércoles»)
Sábado y miércoles:
=O(TEXTO(A1;»dddd»)=»sábado»;O(TEXTO(A1;»dddd»)=»miércoles»)=VERDADERO)
sábado, domingo y miércoles:
=O(TEXTO(A1;»dddd»)=»sábado»;O(TEXTO(A1;»dddd»)=»domingo»);O(TEXTO(A1;»dddd»)=»miércoles»)=VERDADERO)

Otro ejemplo tenemos fechas en la columna C y también en el la columna A rango (A1:A500) y deseamos que se aplique formato a las fechas de la columna A que coinciden con las de la columna C, aplicamos la siguiente fórmula:  = Y( COINCIDIR(A1;$C:$C; 0) > 0 )

En la siguiente fórmula lo que cambia es que las fechas de la columna C, están en el rango, C1:C5, aplicamos la siguiente fórmula:
= Y( COINCIDIR(A1;$C$1:$C$5; 0) > 0 )

Tanto para las celdas, columnas, filas o rango de celdas se le puede poner un nombre, y nos devolverían el mismo resultado que haciendo referencia a las celdas que contienen las fechas.

Por ejemplo, la fórmula anterior el rango de celda era desde la celda C1 hasta la celda C5, podemos seleccionar el rango de estas celdas y le ponemos un nombre (por ejemplo festivos) y nos devolvería el mismo resultado aplicando la siguiente fórmula:  = Y( COINCIDIR(A1;festivos; 0) > 0 )

También obtendríamos un resultado idéntico con la función CONTAR,

=CONTAR.SI($C$1:$C$5;A1)>0,9
=CONTAR.SI(festivos;A1)>0,9

Para resaltar con distinto formato las celdas del mes y año actual, aplicamos la siguiente fórmula:

=TEXTO(A1;»mmmm-aaaa»)=TEXTO(HOY();»mmmm-aaaa»)
Para resaltar la fecha del día actual =(HOY()=A1)
Resaltar el día actual y siguiente: =O((HOY()=A1);HOY()+1=A1)
Resaltar el actual y anterior: =O((HOY()=A1);HOY()-1=A1)

Para resaltar las fechas que se encuentran en el rango (A1:A500), del año 2020 de los meses del 1º, 2º,3º y  4º trimestre, aplicamos las siguientes fórmulas:

1º trimestre =Y(AÑO(A1)=2020;Y(MES(A1)>=1;MES(A1)<=3))
2º trimestre=Y(AÑO(A1)=2020;Y(MES(A1)>=4;MES(A1)<=6))
3º trimestre =Y(AÑO(A1)=2020;Y(MES(A1)>=7;MES(A1)<=9))
4º trimestre=Y(AÑO(A1)=2020;Y(MES(A1)>=9;MES(A1)<=12))

Se puede cambiar en la fórmula el año por otro, si por ejemplo estuviese en la celda C1, aplicaríamos la siguiente fórmula, ejemplo 4º trimestre: 

=Y(AÑO(A1)=$C$1;Y(MES(A1)>=9;MES(A1)<=12))

Para los meses de meses de noviembre y diciembre 2020, aplicamos la siguiente fórmula:

=Y(AÑO(A1)=2020;MES(A1)>=11), año en la celda C1  =Y(AÑO(A1)=$C$1;MES(A1)>=11)

En la siguiente fórmula el año se encuentra en la celda C1 y el mes en la celda D1 (número del 1 al 12), aplicamos la siguiente fórmula: =Y(AÑO(A1)=$C$1;MES(A1)=$D$1)

Si la celda D1, tiene el nombre del mes (de enero a diciembre), aplicamos la siguiente fórmula:

=Y(AÑO(A1)=$C$1;TEXTO((A1);»mmmm»)=$D$1)