Fórmula genérica
=SUMAPRODUCTO((start_date<=end_dates)*(end_date>=start_dates))>1
Explicación
Para resaltar celdas donde las fechas se superponen, puede usar el formato condicional con una fórmula basada en la función SUMAPRODUCTO. En el ejemplo que se muestra, la fórmula en el sur de E6 es:
=SUMAPRODUCTO(($C6<=$D$5:$D$9)*($D6>=$C$5:$C$9))>1
Esta es la misma fórmula utilizada para resaltar filas enteras en la tabla utilizando una regla de formato condicional basada en fórmulas.
Cómo funciona esta fórmula
Considere por un momento cómo funcionan las fechas superpuestas. Para que un proyecto se superponga a las fechas de otros proyectos, deben cumplirse dos condiciones:
1. La fecha de inicio debe ser menor o igual (<=) a al menos otra fecha de finalización y la lista.
2. La fecha de finalización del proyecto debe ser mayor o igual que (> =) al menos otra fecha de inicio en la lista.
Si ambas condiciones son verdaderas, las fechas del proyecto deben superponerse a otro proyecto en esa lista.
La función SUMAPRODUCTO es perfecta para este tipo de prueba porque maneja las comparaciones de matrices con elegancia.
Para verificar la fecha de inicio de un proyecto contra todas las fechas de finalización, utilizamos esta expresión:
($C6<=$D$5:$D$9)
Para verificar la fecha de finalización de un proyecto contra todas las fechas de finalización, utilizamos esta expresión:
($D6>=$C$5:$C$9)
Las matrices resultantes de los valores VERDADERO FALSO se multiplican entre sí dentro de SUMAPRODUCTO. Esto obliga a los resultados VERDADERO y FALSO a 1s y 0s automáticamente, por lo que la fórmula se resuelve así:
=SUMAPRODUCTO({0;1;1;1;1}*{1;1;1;0;0})>1 =SUMAPRODUCTO({0;1;1;0;0})>1 =VERDADERO
0 comentarios:
Publicar un comentario