Generisk formel
=SUMPRODUCT((start_date=start_dates))>1
Resumé
For at fremhæve celler, hvor datoer overlapper hinanden, kan du bruge betinget formatering med en formel baseret på SUMPRODUCT-funktionen. I det viste eksempel er formlen i syd E6:
=SUMPRODUCT(($C6=$C$5:$C$9))>1
Dette er den samme formel, der bruges til at fremhæve hele rækker i tabellen ved hjælp af en formelbaseret betinget formateringsregel.
Forklaring
Overvej et øjeblik, hvordan overlappende datoer fungerer. For at et projekt skal overlappe datoerne for andre projekter, skal to betingelser være opfyldt:
1. Startdatoen skal være mindre end eller lig med (<=) til mindst en anden slutdato og listen.
2. Slutdatoen for projektet skal være større end eller lig med (> =) mindst en anden startdato på listen.
Hvis begge disse betingelser er sande, skal projektdatoerne overlappe et andet projekt på listen.
SUMPRODUCT-funktionen er perfekt til denne type test, fordi den håndterer array-sammenligninger elegant.
For at kontrollere en projekts startdato mod alle slutdatoer bruger vi dette udtryk:
($C6<=$D$5:$D$9)
For at kontrollere et projekts slutdato mod alle slutdatoer bruger vi dette udtryk:
($D6>=$C$5:$C$9)
De resulterende arrays med TRUE FALSE-værdier ganges med hinanden inden for SUMPRODUCT. Dette tvinger de TRUE og FALSE resultater til 1s og 0s automatisk, så formlen løses således:
=SUMPRODUCT((0;1;1;1;1)*(1;1;1;0;0))>1 =SUMPRODUCT((0;1;1;0;0))>1 =TRUE
