Plantilla en hoja de Excel que nos puede servir de ayuda o referencia para aplicar distintos formatos o fórmulas en calendarios a los días del calendario o fechas con que contengan ciertos criterios.
En el calendario mensual podemos cambiar el año o el mes en la Barra de desplazamiento (control ActiveX). haciendo clic en las flechas de la izquierda o derecha para disminuir o aumentar los años o meses. También podemos escribir directamente el año y el mes en la celda correspondiente.
El calendario muestra con distintos formatos el día 25 de cada mes de lunes a sábado. Los domingos muestra con distinto formato sólo el 25 de julio que sería Año Santo Jubilar por coincidir a domingo el 25 de julio.
Para mostrar en el calendario el 25 con distinto formato se aplicó la siguiente fórmula condicional. =Y(B5=FECHA(Anual;7;25);DIASEM(FECHA(Anual;7;25);2)=7)
En la fórmula el nombre Anual se refiere al nombre de la celda que contiene el año, por ello al ir cambiando el año sólo mostrará los domingos que correspondan al 25 de julio.
Si por ejemplo deseamos que lo muestre sólo el año 2021 cambiamos en la fórmula Anual por 2021 y se aplicaría la siguiente fórmula.
=Y(B5=FECHA(2021;7;25);DIASEM(FECHA(2021;7;25);2)=7)
En la fórmula estamos indicando que el día corresponda al mes de julio y el día de la semana sea igual a domingo (7), en vez de aplicar el formato para los domingos deseamos que aplique para los sábados cambiamos en la fórmula el último número =7 por =6, como se indica a continuación.
=Y(B5=FECHA(2021;7;25);DIASEM(FECHA(2021;7;25);2)=6)
Para los viernes sería =5, para los jueves =4, miércoles 3, martes 2 y lunes 1.
En el calendario el primer día que puede corresponder se encuentra en la celda B5 y al aplicar el formato condicional debemos indicar al rango donde pueden aparecer los días, en este caso sería $B$5:$H$10, que es el rango donde puede aparecer los días al cambiar el año o mes en el calendario.
Si por ejemplo deseamos que se aplique para todos los meses el día 25 que corresponda a domingo, aplicamos la siguiente fórmula:
=Y(B5=FECHA(Anual;Mes_;25);DIASEM(FECHA(Anual;Mes_;25);2)=7)
Anual (se refiere al nombre de la celda que contiene el año)
Mes_ (se refiere al nombre de la celda que contiene el mes)
Si el año se encuentra en la celda M1 y el mes en la celda N1, aplicamos la siguiente fórmula: =Y(B5=FECHA($M$1;$N$1;25);DIASEM(FECHA($M$1;$N$1;25);2)=7) devolvería el mismo resultado
Debemos tener en cuenta que el calendario hace referencia al año que tiene el nombre Anual y el mes que tiene el nombre Mes_
En el administrador de fórmulas podemos crear ciertas fórmulas y ponerle el nombre. Por ejemplo, hay un nombre en administrador de fórmulas con el nombre julio y contiene la siguiente fórmula: (=FECHA(Anual;7;25)
Si aplicamos en formato condicional a siguiente fórmula =Y(B5=Julio;DIASEM(Julio;2)=7)
Sería como si aplicamos esta fórmula: =Y(B5=FECHA(Anual;7;25);DIASEM(FECHA(Anual;7;25);2)=7)
En la plantilla aparecen otras fórmulas condicionadas en que va depender del año que muestre el calendario, por ejemplo, para saber cuándo es (Año Santo Jubilar)
Por ejemplo, las siguientes fórmulas nos devolvería el año y si es Año Santo Jubilar o no:
=SI(DIASEM(FECHA(Anual;7;25);1)=1;»Año :» &Anual&» (Año Santo Jubilar)»;»Año :» &Anual&» (No es Año Santo)»)
=SI(DIASEM(FECHA(Anual;7;25);1)=1;»Año :» &Anual&» (Año Santo Jubilar)»;»Año :» &Anual&» (No es Año Santo)»)
=SI((ELEGIR(DIASEM(FECHA(Anual;7;25);2);1;2;3;4;5;6;7)=7);»Año: «&Anual&» (Año Santo Jubilar)»;»Año :» &Anual&» (No es Año Santo)»)
=SI(ELEGIR(DIASEM(FECHA(Anual;7;25);1);1;2;3;4;5;6;7)=1;»Año: «&Anual&» (Año Santo Jubilar)»;»Año :» &Anual&» (No es Año Santo)»)
=SI(ELEGIR(DIASEM(FECHA(Anual;7;25);2);»Lunes»;»Martes»;»Miércoles»;»Jueves»;»Viernes»;»Sábado»;»Domingo»)=»Domingo»;»Año: «&Anual&» (Año Santo Jubilar)»;»Año :» &Anual&» (No es Año Santo)»)
=SI(((DIA(25-DIASEM(FECHA(Anual;7;25))))+1)=DIA(25);»Año: «&Anual&» (Año Santo Jubilar)»;»Año :» &Anual&» (No es Año Santo)»)
=SI((TEXTO(FECHA(Anual;7;25);»dddd»)=»Domingo»)=VERDADERO;»Año :»&Anual&» (Año Santo Jubilar)»;»Año :»&Anual&» (No es Año Santo)»)
Para comprobar si un año tiene 365 o 365 podemos aplicar la siguiente fórmula:
FECHA(Anual;12;31+1)-FECHA(Anual;1;1)
Si el año está en la celda B2, cambiamos Anual por B2
FECHA(B2;12;31+1)-FECHA(B2;1;1)
Si deseamos que nos devuelva el final del mes de febrero
=FIN.MES(FECHA(Anual;2;1); 0)
En la siguiente imagen podemos ver un ejemplo de la plantilla que puede descargar en el enlace que se encuentra debajo de la misma. Para que no se borren las fórmulas la plantilla está protegida, si lo desea la puede desproteger (la contraseña está en blanco).
Clic para Descargar calendario de fórmulas para años Bisiestos y Año Santo Jubilar
Hola Fernando, entiendo que tendrías que aplicar una fórmula condicionada en las celdas correspondientes en las que te devuelva en minutos el margen indicado y después aplicar en estas celdas el conjunto de iconos que deseas aplicar.
Ejemplo lo que tienes que aplicar una fórmula condicionada que te devuelva la diferencia en minutos y una vez que obtengas el resultado deseado según la fórmula ya que te devolverá cierto tiempo en minutos, aplicas el formato condicional el conjunto de iconos con los colores deseados.
Por ejemplo si al aplicar la fórmula te devuelve 1:05 lo que sería una hora y 5 minutos si aplicas a la celda el siguiente formato [mm] te devolvería el tiempo en minutos.
Ejemplo tenemos en una celda 1:05 si aplicamos un formato personalizado y ponemos [mm] cambiaría 1:05 por 65 minutos.
Saludos
Hola, por favor a quien pudiera ayudarme con este problemita: tengo una hoja en la cual tengo 10 tareas diferentes (las cuales empiezan en horarios diferentes) tengo que hacerles seguimiento a cada una de ellas cada 3 horas… quisiera implementar un semáforo que me alerte (por cada tarea) 30 minutos antes de cumplirse las tres horas(ámbar) y al cumplirse la hora (rojo) … el seguimiento debe ser cíclico hasta que ingrese la hora de término de cada tarea.