Generieke formule
=(date2-date1)>n
Samenvatting
Om datums te markeren die "achterstallig" zijn, kunt u een voorwaardelijke opmaakregel gebruiken die controleert of het verschil tussen twee datums groter is dan een bepaald aantal dagen. In het getoonde voorbeeld zijn drie voorwaardelijke opmaakregels toegepast op het bereik D5: C12 met deze formules:
=(D5-C5)<3 // rule 1 (green) =(D5-C5)=10 // rule 3 (red)
Opmerking: regels voor voorwaardelijke opmaak worden geëvalueerd ten opzichte van de cel linksboven in de selectie op het moment dat de regel wordt gemaakt, in dit geval D5.
Variantie in dagen berekenen
De varianties in kolom E worden berekend door de oorspronkelijke datum af te trekken van de huidige datum met deze formule:
=D5-C5
Het resultaat is het verschil in dagen tussen de oorspronkelijke datum en de huidige datum. Een positief verschil vertegenwoordigt een late datum (dwz een "slip" in het schema). Een negatief verschil geeft aan dat de huidige datum voorloopt op schema. Dit werkt, omdat Excel-datums serienummers zijn.
De variantie die wordt weergegeven in kolom E is alleen ter referentie in dit voorbeeld en wordt niet gebruikt door de regels voor voorwaardelijke opmaak. Als u kolom E echter als een hulpkolom behandelt, kunt u eenvoudiger regels voor voorwaardelijke opmaak schrijven die de variantie rechtstreeks gebruiken.
Uitleg
In dit voorbeeld willen we drie verschillende kleuren toepassen, afhankelijk van hoeveel de oorspronkelijke datum verschilt van de huidige datum:
- Groen als de variantie minder dan 3 dagen is
- Geel als de variantie tussen de 3 en 10 dagen ligt
- Rood als de variantie groter is dan 10 dagen
Voor elke regel berekenen we een variantie door de oorspronkelijke datum af te trekken van de "huidige" datum (zoals hierboven uitgelegd). Vervolgens controleren we het resultaat met een logische uitdrukking. Wanneer een uitdrukking WAAR retourneert, wordt de voorwaardelijke opmaak geactiveerd.
Omdat we drie afzonderlijke kleuren willen, elk met een logische test, hebben we drie afzonderlijke regels voor voorwaardelijke opmaak nodig. Het onderstaande scherm laat zien hoe de regels zijn geconfigureerd om de groene, gele en rode opmaak toe te passen. Merk op dat bij de eerste twee regels "stop indien waar" is aangevinkt:
Regels worden geëvalueerd in de aangegeven volgorde. Regel 1 test of de variantie minder dan 3 dagen is. Regel 2 controleert of de afwijking minder dan 10 dagen is. Regel 3 controleert of de variantie groter is dan of gelijk is aan 10 dagen. Zowel regel 1 als regel 2 hebben "stop indien waar" ingeschakeld. Wanneer een van beide regels WAAR retourneert, stopt Excel met het controleren van aanvullende regels.
N dagen te laat vanaf vandaag
Misschien wilt u een vervaldatum vergelijken met de datum van vandaag. Om te testen of datums met ten minste n dagen te laat zijn vanaf vandaag, kunt u een formule als deze gebruiken:
=(TODAY()-date)>=n
Deze formule retourneert alleen WAAR als een datum ten minste n dagen in het verleden ligt. Als een datum in de toekomst ligt, is het verschil een negatief getal, dus de regel zal nooit worden geactiveerd.
Zie 50+ voorbeelden van formulecriteria voor meer informatie over het samenstellen van formulecriteria.
