Generieke formule
=SUMPRODUCT((start_date=start_dates))>1
Samenvatting
Om cellen te markeren waar datums elkaar overlappen, kunt u voorwaardelijke opmaak gebruiken met een formule op basis van de functie SOMPRODUCT. In het getoonde voorbeeld is de formule in zuid E6:
=SUMPRODUCT(($C6=$C$5:$C$9))>1
Dit is dezelfde formule die wordt gebruikt om hele rijen in de tabel te markeren met behulp van een op formules gebaseerde voorwaardelijke opmaakregel.
Uitleg
Bedenk eens hoe overlappende datums werken. Om een project de data van andere projecten te laten overlappen, moet aan twee voorwaarden worden voldaan:
1. De startdatum moet kleiner zijn dan of gelijk zijn aan (<=) aan ten minste één andere einddatum en de lijst.
2. De einddatum van het project moet groter zijn dan of gelijk zijn aan (> =) minimaal één andere startdatum in de lijst.
Als aan beide voorwaarden wordt voldaan, moeten de projectdatums een ander project in die lijst overlappen.
De SUMPRODUCT-functie is perfect voor dit soort tests omdat het op elegante wijze omgaat met array-vergelijkingen.
Om de startdatum van een project tegen alle einddatums te controleren, gebruiken we deze uitdrukking:
($C6<=$D$5:$D$9)
Om de einddatum van een project tegen alle einddatums te controleren, gebruiken we deze uitdrukking:
($D6>=$C$5:$C$9)
De resulterende matrices van TRUE FALSE-waarden worden binnen SOMPRODUCT met elkaar vermenigvuldigd. Dit dwingt de TRUE en FALSE resultaten automatisch in 1s en 0s, dus de formule wordt als volgt opgelost:
=SUMPRODUCT((0;1;1;1;1)*(1;1;1;0;0))>1 =SUMPRODUCT((0;1;1;0;0))>1 =TRUE
