¿Alguna vez te has encontrado lidiando con un mar de fechas y horas en tus hojas de cálculo de Excel, sintiendo que el caos se apodera de tus datos? ¡No te preocupes más! En este artículo, descubrirás el poderoso formato condicional de Excel como tu mejor aliado para dar sentido y orden a esa información. Te enseñaremos cómo aplicar fórmulas y establecer reglas que transformarán tu manera de trabajar, permitiéndote resaltar automáticamente las fechas importantes, identificar plazos críticamente y organizar tus horarios de manera eficiente. Prepárate para llevar tus habilidades en Excel al siguiente nivel y convertirte en un maestro del análisis de datos.
El formato condicional en Excel es una herramienta poderosa que te permitirá resaltar automáticamente las fechas y horas en tus hojas de cálculo. Ya sea que estés planificando una reunión importante o siguiendo el tiempo transcurrido de un proyecto, el formato condicional te ayudará a visualizar de manera clara y eficiente esta información. En este artículo, aprenderás cómo utilizar fórmulas y reglas en Excel para aplicar el formato condicional a tus datos de fechas y horas. Descubre cómo aprovechar al máximo esta función para mejorar tus análisis y presentaciones de datos.
Si es un visitante habitual de este blog, probablemente haya notado algunos artículos que cubren diferentes aspectos del formato condicional de Excel. Y ahora aprovecharemos este conocimiento y crearemos hojas de cálculo que diferencien entre días laborables y fines de semana, resalten los días festivos y muestren una próxima fecha límite o retraso. En otras palabras, aplicaremos el formato condicional de Excel a las fechas.
Si tiene algunos conocimientos básicos de las fórmulas de Excel, lo más probable es que esté familiarizado con algunas funciones de fecha y hora, como AHORA, HOY, FECHA, DÍA DE LA SEMANA, etc. En este tutorial, llevaremos esta funcionalidad un paso más allá para formatear condicionalmente las fechas de Excel en la forma que quieras.
Fórmulas de formato condicional de Excel para fechas.
Si desea resaltar celdas o filas enteras basado en una fecha en otra celdao crear reglas para mayores intervalos de tiempo (es decir, más de un mes desde la fecha actual), tendrás que crear tu propia regla de formato condicional basada en una fórmula. A continuación encontrará algunos ejemplos de mis formatos condicionales de Excel favoritos para fechas.
Cómo resaltar los fines de semana en Excel
Lamentablemente, Microsoft Excel no tiene un calendario integrado similar al de Outlook. Bueno, veamos cómo puedes crear tu propio calendario automatizado con bastante poco esfuerzo.
Al diseñar su calendario de Excel, puede utilizar la función =FECHA(año,mes,fecha) para mostrar los días de la semana. Simplemente ingrese el año y el número del mes en algún lugar de su hoja de cálculo y haga referencia a esas celdas en la fórmula. Por supuesto, puedes escribir los números directamente en la fórmula, pero este no es un método muy eficiente porque tendrías que ajustar la fórmula para cada mes.
La siguiente captura de pantalla muestra la función FECHA en acción. usé la fórmula =DATE($B$2,$B$1,B$4) que se copia en la fila 5.
>
Consejo. Si desea mostrar solo los días de la semana como ve en la imagen de arriba, seleccione las celdas con la fórmula (fila 5 en nuestro caso), haga clic derecho y elija Formato de celdas…> Número> Personalizado. De la lista desplegable debajo Tiposeleccione dddd o dddd para mostrar nombres de días completos o nombres abreviados, respectivamente.
Su calendario de Excel está casi terminado y solo necesita cambiar el color de los fines de semana. Naturalmente, no vas a colorear las celdas manualmente. Haremos que Excel formatee los fines de semana automáticamente creando una regla de formato condicional basada en el DÍA LABORABLE fórmula.
>Ahora, déjame explicarte brevemente el WEEKDAY(serial_number,[return_type]) fórmula para que pueda ajustarla rápidamente a sus propias hojas de cálculo.
- El serial_number El parámetro representa la fecha que está intentando encontrar. Ingresas una referencia a tu primera celda con una fecha, B$5 en nuestro caso.
- El [return_type] El parámetro determina el tipo de semana (los corchetes implican que es opcional). Ingresa 2 como tipo de devolución para una semana que comienza del lunes (1) al domingo (7). Puede encontrar la lista completa de tipos de devolución disponibles aquí.
- Finalmente, escribe >5 para resaltar solo los sábados (6) y domingos (7).
La siguiente captura de pantalla muestra el resultado en Excel 2013: los fines de semana están resaltados en color rojizo.
>Consejos:
- Si tiene fines de semana no estándar en su empresa, por ejemplo, viernes y sábados, deberá modificar la fórmula para que comience a contar desde el domingo (1) y resaltar los días 6 (viernes) y 7 (sábado). WEEKDAY(B$5,1)>5.
- Si está creando un calendario horizontal (apaisado), use una columna relativa (sin $) y una fila absoluta (con $) en una referencia de celda porque debe bloquear la referencia de la fila; en el ejemplo anterior es la fila 5, por lo que ingresamos B$5. Pero si estás diseñando un calendario en orientación vertical, debes hacer lo contrario, es decir, usar una columna absoluta y una fila relativa, por ejemplo, $B5, como puedes ver en la siguiente captura de pantalla:
>Cómo resaltar días festivos en Excel
Para mejorar aún más su calendario de Excel, también puede sombrear los días festivos. Para hacer eso, deberá enumerar los días festivos que desea resaltar en la misma hoja de cálculo o en alguna otra.
Por ejemplo, agregué los siguientes días festivos en la columna A ($A$14:$A$17). Por supuesto, no todos son verdaderos días festivos, pero servirán a modo de demostración : )
>De nuevo abres Formato condicional > Nueva regla. En el caso de días festivos, vas a utilizar cualquiera de los dos FÓSFORO o CONTAR.SI función:
- =COUNTIF($A$14:$A$17,B$5)>0
- =MATCH(B$5,$A$14:$A$17,0)
Nota. Si ha elegido un color diferente para los días festivos, debe mover la regla de días festivos a la parte superior de la lista de reglas a través de Formato condicional > Administrar reglas…
La siguiente imagen muestra el resultado en Excel 2013:
>Formatear condicionalmente una celda cuando un valor se cambia a una fecha
No es un gran problema formatear condicionalmente una celda cuando se agrega una fecha a esa celda o a cualquier otra celda en la misma fila, siempre que no se permita ningún otro tipo de valor. En este caso, simplemente podría usar una fórmula para resaltar lo que no está en blanco, como se describe en Fórmulas condicionales de Excel para espacios en blanco y lo que no es en blanco. Pero, ¿qué pasa si esas celdas ya tienen algunos valores, por ejemplo, texto, y desea cambiar el color de fondo cuando el texto se cambia a una fecha?
La tarea puede parecer un poco complicada, pero la solución es muy sencilla.
- D1: dd-mmm-aa o d-mmm-aa
- D2: dd-mmm o d-mmm
- D3: mmm-aa
- D4: mm/dd/aa o m/d/aa o m/d/aa h:mm
Puede encontrar la lista completa de códigos de fecha en Este artículo.
Si su tabla contiene fechas en 2 o más formatos, utilice el operador O, por ejemplo =OR(cell(«format», $A2)=»D1″, cell(«format»,$A2)=»D2″, cell(«format», $A2)=»D3″)
La siguiente captura de pantalla muestra el resultado de dicha regla de formato condicional para las fechas.
>
Cómo resaltar filas según una fecha determinada en una columna determinada
Supongamos que tiene una hoja de cálculo de Excel grande que contiene dos columnas de fecha (B y C). Desea resaltar cada fila que tenga una fecha determinada, digamos 13-mayo-14, en la columna C.
Para aplicar el formato condicional de Excel a una fecha determinada, necesita encontrar su valor numérico primero. Como probablemente sepa, Microsoft Excel almacena las fechas como números de serie secuenciales, a partir del 1 de enero de 1900. Entonces, 1 de enero de 1900 se almacena como 1, 2 de enero de 1900 se almacena como 2… y 13 de mayo de 14 como 41772.
Para encontrar el número de fecha, haga clic derecho en la celda, seleccione Dar formato a celdas > Número y elige el General formato. Anota el número que ves y haz clic Cancelar porque realmente no deseas cambiar el formato de la fecha.
>En realidad, esa fue la mayor parte del trabajo y ahora sólo necesitas crear una regla de formato condicional para toda la tabla con esta fórmula muy simple: =$C2=41772. La fórmula implica que su tabla tiene encabezados y la fila 2 es la primera fila con datos.
Una forma alternativa es utilizar el VALOR FECHA La fórmula que convierte la fecha al formato numérico es la que se almacena, por ejemplo. =$C2=DATEVALUE(«5/13/2014»)
Cualquiera que sea la fórmula que utilices, tendrá el mismo efecto:
>Formatear condicionalmente fechas en Excel según la fecha actual
Como probablemente sepa, Microsoft Excel proporciona la TODAY() funciones para varios cálculos basados en la fecha actual. Estos son solo algunos ejemplos de cómo puede usarlo para formatear fechas condicionalmente en Excel.
Ejemplo 1. Resaltar fechas iguales, mayores o menores que hoy
Para formatear condicionalmente celdas o filas completas según la fecha de hoy, utilice la función HOY de la siguiente manera:
Igual a hoy: =$B2=TODAY()
Mayor que hoy: =$B2>TODAY()
Menos que hoy: =$B2<TODAY()
La siguiente captura de pantalla muestra las reglas anteriores en acción. Tenga en cuenta que, en el momento de escribir este artículo, HOY era el 12 de junio de 2014.
>Ejemplo 2. Formatear fechas condicionalmente en Excel según varias condiciones
De manera similar, puede usar la función HOY en combinación con otras funciones de Excel para manejar escenarios más complejos. Por ejemplo, es posible que desee que la fórmula de fecha de formato condicional de Excel coloree el Factura columna cuando el Fecha de entrega es igual o mayor que hoy PERO desea que el formato desaparezca cuando ingrese el número de factura.
Para esta tarea, necesitaría una columna adicional con la siguiente fórmula (donde E es su Entrega columna y F la Factura columna):
=IF(E2>=TODAY(),IF(F2=»», 1, 0), 0)
Si la fecha de entrega es mayor o igual a la fecha actual y no hay ningún número en la columna Factura, la fórmula devuelve 1; de lo contrario, es 0.
Después de eso, crea una regla de formato condicional simple para el Factura columna con la fórmula =$G2=1 donde G es su columna adicional. Por supuesto, podrás ocultar esta columna más adelante.
>Ejemplo 3. Resalte las próximas fechas y retrasos
Suponga que tiene un cronograma de proyecto en Excel que enumera las tareas, sus fechas de inicio y duraciones. Lo que desea es que la fecha de finalización de cada tarea se calcule automáticamente. Un desafío adicional es que la fórmula también debería considerar los fines de semana. Por ejemplo, si la fecha de inicio es el 13 de junio de 2014 y el número de días de trabajo (Duración) es 2, la fecha de finalización debería ser el 17 de junio de 2014, porque el 14 de junio y el 15 de junio son sábado y domingo. .
Para ello utilizaremos el WORKDAY.INTL(start_date,days,[weekend],[holidays]) función, más precisamente =WORKDAY.INTL(B2,C2,1).
>En la fórmula, ingresamos 1 como 3tercero parámetro ya que indica sábado y domingo como festivos. Puede utilizar otro valor si sus fines de semana son diferentes, digamos viernes y sábado. La lista completa de los valores del fin de semana está disponible. aquí. Opcionalmente, también puedes utilizar el cuarto parámetro. [holidays]que es un conjunto de fechas (rango de celdas) que deben excluirse del calendario de días laborables.
Y, por último, es posible que desees resaltar filas dependiendo de qué tan lejos esté la fecha límite. Por ejemplo, las reglas de formato condicional basadas en las siguientes dos fórmulas resaltan las fechas de finalización próximas y recientes, respectivamente:
- =AND($D2-TODAY()>=0,$D2-TODAY()<=7) – resalte todas las filas donde la Fecha de finalización (columna D) está dentro del los próximos 7 días. Esta fórmula es realmente útil cuando se trata de realizar un seguimiento de las próximas fechas de vencimiento o pagos.
- =AND(TODAY()-$D2>=0,TODAY()-$D2<=7) – resalte todas las filas donde la Fecha de finalización (columna D) está dentro del Los últimos 7 días. Puede utilizar esta fórmula para realizar un seguimiento de los últimos pagos vencidos y otros retrasos.
>Aquí hay algunos ejemplos de fórmulas más que se pueden aplicar a la tabla anterior:
=$D2<TODAY() – resalta todas las fechas pasadas (es decir, fechas inferiores a la fecha actual). Se puede utilizar para formatear suscripciones vencidas, pagos vencidos, etc.
=$D2>TODAY() – resalta todas las fechas futuras (es decir, fechas mayores que la fecha actual). Puede usarlo para resaltar los próximos eventos.
Por supuesto, puede haber infinitas variaciones de las fórmulas anteriores, dependiendo de su tarea particular. Por ejemplo:
=$D2-TODAY()>=6 – destaca fechas que ocurren en 6 o más días.
=$D2=TODAY()-14 – destaca fechas que ocurrieron hace exactamente 2 semanas.
Cómo resaltar fechas dentro de un rango de fechas
Si tiene una lista larga de fechas en su hoja de trabajo, es posible que también desee resaltar las celdas o filas que se encuentran dentro de un determinado rango de fechas, es decir, resaltar todas las fechas que se encuentran entre dos fechas determinadas.
Puede realizar esta tarea utilizando la función HOY() nuevamente. Sólo tendrá que construir fórmulas un poco más elaboradas, como se demuestra en los ejemplos siguientes.
Fórmulas para resaltar fechas pasadas
- Hace más de 30 días: =TODAY()-$A2>30
- De hace 30 a 15 días inclusive: =AND(TODAY()-$A2>=15, TODAY()-$A2<=30)
- Hace menos de 15 días: =AND(TODAY()-$A2>=1, TODAY()-$A2<15)
La fecha actual y las fechas futuras no están coloreadas.
>Fórmulas para resaltar fechas futuras
- Ocurrirá en más de 30 días a partir de ahora: =$A2-TODAY()>30
- En 30 a 15 días, inclusive: =AND($A2-TODAY()>=15, $A2-TODAY()<=30)
- En menos de 15 días: =AND($A2-TODAY()>=1, $A2-TODAY()<15)
La fecha actual y las fechas pasadas no están coloreadas.
>Cómo sombrear espacios e intervalos de tiempo
En este último ejemplo, utilizaremos otra función de fecha de Excel: DATEDIF(start_date, end_date, interval). Esta función calcula la diferencia entre dos fechas en función del intervalo especificado. Se diferencia de todas las demás funciones que hemos comentado en este tutorial en que te permite ignorar meses o años y calcular la diferencia sólo entre días o meses, lo que elijas.
¿No ves cómo esto podría funcionar para ti? Piénselo de otra manera… Suponga que tiene una lista de cumpleaños de sus familiares y amigos. ¿Quieres saber cuántos días faltan para su próximo cumpleaños? Además, ¿cuántos días quedan exactamente para tu aniversario de boda y otros eventos que no querrás perderte? ¡Fácilmente!
La fórmula que necesitas es esta (donde A es tu Fecha columna):
=DATEDIF(TODAY(), DATE((YEAR(TODAY())+1), MONTH($A2), DAY($A2)), «yd»)
El tipo de intervalo «yd» al final de la fórmula se utiliza para ignorar los años y calcular la diferencia entre los días únicamente. Para obtener la lista completa de tipos de intervalos disponibles, consulte aquí.
Consejo. Si olvida o extravía esa fórmula compleja, puede utilizar esta sencilla en su lugar: =365-DATEDIF($A2,TODAY(),»yd»). Produce exactamente los mismos resultados, sólo recuerda reemplazar 365 por 366 en años bisiestos 🙂
Y ahora creemos una regla de formato condicional de Excel para sombrear diferentes espacios en diferentes colores. En este caso, tiene más sentido utilizar escalas de colores de Excel en lugar de crear una regla separada para cada período.
La siguiente captura de pantalla muestra el resultado en Excel: una escala degradada de 3 colores con tintes que van del verde al rojo y al amarillo.
>Aplicación web de Excel «Días hasta el próximo cumpleaños»
Hemos creado esta aplicación web de Excel para mostrarle la fórmula anterior en acción. Simplemente ingrese sus eventos en la primera columna y cambie las fechas correspondientes en la segunda columna para experimentar con el resultado.
Nota. Para ver el libro integrado, permita las cookies de marketing.
Si tiene curiosidad por saber cómo crear hojas de cálculo de Excel interactivas, consulte este artículo sobre cómo crear hojas de cálculo de Excel basadas en la web.