Cómo configurar el formato condicional en Excel para fechas

El Formato condicional en Excel puede considerarse una utilidad valiosa que puede ayudar a mejorar visualmente el análisis y la presentación de los datos en la hoja de cálculo mediante la aplicación de un formato particular basado en reglas o condiciones predefinidas. Esta publicación compartirá cómo puede configurar el formato condicional en Excel para fechas. También hemos incluido ejemplos detallados que puede probar mientras lee la guía.

Cómo configurar el formato condicional en Excel para fechas

El formato condicional no sólo ayuda a resaltar las celdas mediante códigos de color, sino que también da formato a las filas, columnas y celdas que constan de fechas de una manera que pueda considerarse visualmente significativa y atractiva.

1. Predefinido
2. Usuario definido
3. Crear una regla de formato personalizada
4. Destacando días festivos en Excel
5. Formato condicional de fechas basado en múltiples condiciones
6. Destacando las próximas fechas y retrasos

Asegúrese de revisar detenidamente los detalles en Excel. Ofrece muchas opciones, algunas de las cuales pueden estar fuera del alcance de esta guía.

1]Reglas de fecha de formato condicional predefinidas con Fechas

El formato condicional con fechas puede resultar beneficioso al tratar con conjuntos de datos que incluyen información relacionada con fechas. Excel ofrece casi diez opciones diferentes para formatear celdas según fechas, como se ilustra a continuación:

• Para formatear una celda con la fecha como tipo de datos, haga clic en Inicio > Formato condicional > Resalte reglas de celda >Una fecha en la que ocurre

• Las opciones de formato condicional se pueden elegir en los menús desplegables de la siguiente ventana. El primer menú desplegable nos permite seleccionar el rango de días, y desde el segundo se pueden elegir las opciones de formato.

• Haga clic en Aceptar para aplicar las reglas de formato elegidas.

2]Reglas de fecha de formato condicional definidas por el usuario con Fechas

Además de un conjunto de reglas predefinidas, Excel también ofrece la opción de personalizar estas reglas según los requisitos del usuario. Hay varias formas de configurar las personalizaciones, como se ilustra a continuación:

Usando formato personalizado

1. Abra la ventana de formato Una fecha que ocurre siguiendo los pasos mencionados anteriormente.
2. Después de seleccionar el rango de días, seleccione Formato personalizado en el menú desplegable de opciones de formato.
3. La ventana Formato de celdas aparecerá debido a lo anterior, donde podemos elegir el color, el borde, la fuente y los efectos de nuestro elección y luego haga clic en Aceptar para aplicar los cambios.

Crear nuevas reglas

Este método implica la creación de una regla completamente nueva para el formato condicional, a diferencia del método anterior, en el que podemos elegir entre algunas reglas predefinidas y crear solo un formato personalizado. Crear una nueva regla implica:

• Seleccionar las celdas a las que se aplicará el formato.
• En la pestaña Inicio, haga clic en Formato condicional > Nueva regla, que debería abrir la ventana Nueva regla de formato.

• En dicha ventana, podemos elegir entre los Tipos de reglas disponibles, seguido de editar la descripción de la regla para configurar los criterios para condiciones. formato.
• Una vez completado, se puede hacer clic en el botón Formato en la esquina inferior derecha de la ventana para elegir el formato para dicha regla.
• En la ventana Formato de celdas que se abre, podemos elegir la fuente, el borde, el color de la celda, los efectos, etc., según opción y haga clic en Aceptar una vez en esta ventana, seguido de la ventana Nueva regla de formato para que se apliquen los cambios.

3]Cómo crear una regla de formato personalizada con fechas

En el ejemplo anterior, navegamos por los pasos para crear una nueva regla para formato condicional según los criterios ya proporcionados. Excel también permite aplicar dichas reglas de formato según criterios definidos por el usuario.

Por ejemplo, supongamos que necesitamos resaltar las celdas con fechas que tienen más de 30 o 60 días. En ese caso, lo mismo también se puede implementar aplicando fórmulas y funciones de fecha disponibles en Excel, como se ilustra a continuación:

A] Resalte las fechas actuales y anteriores

En Seleccionar una nueva regla en la opción Formato condicional, elija Usar una fórmula para determinar qué celda a formatear, escriba:

=$H4=TODAY() para resaltar el día actual

=$H4>TODAY() para resaltar días futuros

=$H4<TODAY() para resaltar los días anteriores

=$H4<=(TODAY()-30) para resaltar fechas que tienen más de 30 días.

=$H4<=(TODAY()-60) para resaltar fechas que tienen más de 60 días.

La fórmula anterior verifica el rango de fechas seleccionado para la fecha actual y, una vez que se encuentra la coincidencia, cambia el color de fondo a Púrpura según lo seleccionado en la opción Formato. El $ usado junto a H significa que la columna permanecería constante, pero las filas serían variables.

• Una vez que se menciona la Descripción de la regla, se pueden elegir la fuente, los colores y los efectos haciendo clic en el botón Formato.
• Haga clic en Aceptar en la ventana Formato de celdas, seguido de lo mismo en la ventana Nueva regla, para que los cambios sean efectivos.< /span>

B] Resaltado basado en un rango de fechas definido por el usuario

Se pueden aplicar métodos similares para formatear los rangos de fechas especificados por los usuarios. Por ejemplo, si necesitamos resaltar las fechas que tienen más de 30 días pero menos de 45 días, la función HOY() se puede usar en combinación con un operador lógico como AND para especificar los criterios que involucran dos condiciones:

• Seleccione las celdas a las que se debe aplicar el formato y abra la ventana Nueva regla.
• En el campo Editar la descripción de la regla, ingrese la siguiente fórmula,

=AND(H2>=(TODAY()-45),H2<=(TODAY()-30))

La fórmula anterior utiliza el operador AND para resaltar las fechas en amarillo que tienen más de 30 días pero menos de 45 días para el selección completa, comenzando desde la celda no. H2.

C] Destacando los fines de semana

En el mismo rango, utilizando la fórmula WEEKDAY(), también se pueden resaltar las fechas que caen en fin de semana:

• Seleccione las celdas a las que se debe aplicar el formato y abra la ventana Nueva regla.
• En el campo Editar la descripción de la regla, ingrese la siguiente fórmula,

=WEEKDAY($H2, 2)>5

La sintaxis de la fórmula del día de la semana se puede representar como DÍA DE LA SEMANA (número de serie, [tipo de retorno]), donde número de serie representa el número de celda del fecha que estamos intentando comprobar (SA1 en este caso).

El parámetro return_types representa el tipo para la semana que comienza del lunes (1) al domingo (7), considerando el día de inicio de la semana. como lunes (representado por 1) y el último día de la semana como domingo (representado por 7).

Sin embargo, los tipos de retorno son opcionales en la fórmula y se representan entre terceras llaves. >5 se utiliza para resaltar el sábado (6) y el domingo (7) para el tipo de devolución especificado.

D] Resaltado de filas según una fecha específica

Esta opción puede ser útil cuando se manejan grandes cantidades de datos en los que es necesario resaltar una fecha en particular. Lo mismo se puede lograr determinando el valor numérico de la fecha o usando la función DATEVALUE(). El valor numérico debe determinarse inicialmente para aplicar formato condicional para una fecha específica, ya que Excel almacena permanentemente las fechas como números de serie secuenciales. Para encontrar el valor numérico de una fecha específica,

• Haga clic derecho en la celda con la fecha, haga clic en Formato de celdas >Número y luego elija General. Se mostrará un valor numérico de 5 dígitos en la celda, que se puede anotar para referencia futura.

• Haga clic en Cancelar una vez que haya anotado el número, ya que no necesitamos cambiar el formato de fecha de la celda.
• Haga clic en Formato condicional>Nueva regla> Seleccione una nueva regla> Utilice una fórmula para determinar a qué celda formatear y escriba la siguiente fórmula,

=$G5=45249

• Usando la DATEVALUE() función, también se puede hacer lo mismo ingresando la fórmula que se menciona a continuación,

=$G5=DATEVALUE("11/19/2023")

El $ usado al lado de C significa que la columna permanecería constante, pero las filas serían variables.

4] Resaltado de días festivos en Excel

Dado que los días festivos pueden variar según la demografía, Excel no tiene ninguna función integrada para resaltar los días festivos. Por lo tanto, para identificar los días festivos, debemos registrar las fechas en una columna diferente y aplicar la fórmula countif para verificar la aparición de las fechas festivas. en la hoja de datos.

Por ejemplo, tenemos algunas fechas mencionadas en la columna D y una lista de días festivos en la columna G. Mediante el formato condicional, podemos resaltar los días festivos en ROJO en la columna D, si los hay, comparándolos con las fechas de días festivos mencionadas en la columna G. siguiendo los pasos que se mencionan a continuación:

• Abra el cuadro de diálogo Nueva regla y elija Usar una fórmula para determinar qué celdas formatear < /span>Seleccione tipo de regla.en
• Ingrese la fórmula =CONTAR.SI($G$3:$G$5,$D3)
• Haga clic en Formato > Rellene y seleccione el color ROJO de la paleta y haga clic en Aceptar< /span>.

countif() verifica la aparición de las fechas mencionadas en las celdas G3 – G5 con las fechas mencionadas en la columna D a partir de la celda D3. .

5]Formato condicional de fechas basado en múltiples condiciones

Supongamos una hoja de trabajo en la que tenemos quejas sobre varios artículos del hogar. La hoja presenta las fechas en que se han planteado las quejas y las fechas de vencimiento y cierre, según corresponda.

Utilizando el formato condicional, marquemos las quejas en BLANCO para las que han sido CERRADAS, en ROJO para las que han pasado la fecha de vencimiento pero aún no cerradas y en AZUL para las que aún han llegado a la fecha de vencimiento.

El proceso implicaría crear dos reglas de formato condicional para los mismos datos, una para aquellos que aún no han llegado a la fecha de vencimiento y otra para aquellos que la han superado. Para hacerlo,

• Abra la ventana Nueva regla de formato condicional y seleccione Usar una fórmula para determinar qué celdas formatear en Seleccione un tipo de regla.
• Ingrese la fórmula que se menciona a continuación

=AND($I10="",$H10>$K$6)

• Haga clic en Formato, seleccione el color AZUL de la paleta y haga clic en Aceptar.
• Cree otra Nueva regla con el mismo tipo de regla e ingrese la fórmula que se menciona a continuación

=AND($I10="",$H10<$K$6)

• Haga clic en Formato, seleccione el color ROJO de la paleta y haga clic en Aceptar.

En esta fórmula, primero verificamos si la columna Fecha de cierre está EN BLANCO o no para determinar que la queja aún está abierta Y luego verificamos si la FECHA DE VENCIMIENTO es mayor que la fecha actual o menor para determinar si la fecha de vencimiento se acerca ($H10>$K$6) o se supera ($ H10<$K$6). La fecha en la celda K6 representa la fecha actual.

6] Destacando las próximas fechas y retrasos

Para entender cómo podemos resaltar retrasos con respecto a las fechas en una hoja de Excel, consideremos un ejemplo en el que tenemos una lista de acciones con sus nombres y fechas de vencimiento. Mediante formato condicional, si necesitamos marcar en VERDE los que están por caducar y en ROJO los que ya han caducado,

• Seleccione el rango de datos y abra el cuadro de diálogo Nueva regla.
• Elija Dar formato solo a las celdas que contienen en Seleccionar tipo de regla
• En Editar descripción de regla, elija Valor de celda. Mayor que, =Now()+30 en los respectivos menús desplegables.
• Haga clic en Formato, seleccione VERDE en la paleta de colores, y haga clic en Aceptar.

• Nuevamente, abra una Nueva regla y elija el mismo Tipo de regla, <. a i=4>como arriba.
• En Editar descripción de regla, elija Valor de celda. Menos de, =Ahora() en los respectivos menús desplegables. un>
• Haga clic en Formato y seleccione ROJO del color paleta y haga clic en Aceptar.

Conclusión

Este tutorial tiene como objetivo cubrir de manera integral las opciones y funciones que se pueden usar para aplicar formato condicional en hojas de cálculo de Excel basadas en fechas. Puede ayudar a comprender cómo aplicar reglas de formato para mejorar la visualización y el análisis de datos. Dominar el formato condicional también puede ayudar a identificar tendencias, verificar plazos y establecer prioridades de tareas, lo que ayuda a tomar decisiones informadas.

¿Cómo creo formato condicional en Excel basado en fechas?

Primero, seleccione sus celdas de fecha para crear formato condicional en Excel basado en fechas. Haga clic en «Inicio» > ‘Formato condicional’ > ‘Nueva regla’. Elija «Dar formato a las celdas que contienen», seleccione «Fechas que ocurren» en la regla, elija los criterios de fecha específicos y luego establezca su formato preferido haciendo clic en «Formato». Aplicar presionando «Aceptar». Esto resalta rápidamente fechas clave en sus datos.

¿Cómo doy formato condicional a las fechas de vencimiento en Excel?

Primero, seleccione sus celdas con fechas para darle formato condicional a las fechas de vencimiento en Excel. Luego, haga clic en «Formato condicional» en la pestaña «Inicio». Elija «Nueva regla», seleccione «Formatear solo las celdas que contienen», establezca la regla en «Valor de celda» y «menor que» e ingrese su fecha de vencimiento o una fórmula. Haga clic en «Formato», elija su estilo de formato y presione «Aceptar» para aplicar.