Generisk formel
=(date2-date1)>n
Resumé
For at fremhæve datoer, der er "forfaldne", kan du bruge en betinget formateringsregel, der kontrollerer, om variansen mellem to datoer er større end et bestemt antal dage. I det viste eksempel er der anvendt tre betingelser for betinget formatering i området D5: C12 med disse formler:
=(D5-C5)<3 // rule 1 (green) =(D5-C5)=10 // rule 3 (red)
Bemærk: betingede formateringsregler evalueres i forhold til den øverste venstre celle i markeringen på det tidspunkt, hvor reglen oprettes, i dette tilfælde D5.
Beregning af varians i dage
Afvigelserne i kolonne E beregnes ved at trække den oprindelige dato fra den aktuelle dato med denne formel:
=D5-C5
Resultatet er forskellen i dage mellem den oprindelige dato og den aktuelle dato. En positiv forskel repræsenterer en sen dato (dvs. en "slip" i tidsplanen). En negativ forskel angiver, at den aktuelle dato er forud for tidsplanen. Dette fungerer, fordi Excel-datoer er serienumre.
Variansen vist i kolonne E er kun til reference i dette eksempel og bruges ikke af de betingede formateringsregler. Men hvis du behandler kolonne E som en hjælpekolonne, kan du skrive enklere betingede formateringsregler, der bruger variansen direkte.
Forklaring
I dette eksempel ønsker vi at anvende tre forskellige farver, afhængigt af hvor meget den originale dato varierer fra den aktuelle dato:
- Grøn, hvis variansen er mindre end 3 dage
- Gul, hvis variansen er mellem 3 og 10 dage
- Rød, hvis variansen er større end 10 dage
For hver regel beregner vi en varians ved at trække den oprindelige dato fra den "aktuelle" dato (som forklaret ovenfor). Derefter kontrollerer vi resultatet med et logisk udtryk. Når et udtryk returnerer SAND, udløses den betingede formatering.
Fordi vi vil have tre separate farver, hver med en logisk test, har vi brug for tre separate betingede formateringsregler. Skærmen nedenfor viser, hvordan reglerne er konfigureret til at anvende den grønne, gule og røde formatering. Bemærk, at de to første regler er markeret med "stop if true":
Regler evalueres i den viste rækkefølge. Regel 1 tester, om variansen er mindre end 3 dage. Regel 2 kontrollerer, om variansen er mindre end 10 dage. Regel 3 kontrollerer, om variansen er større end eller lig med 10 dage. Både regel 1 og regel 2 er aktiveret "stop if true". Når hver regel returnerer SAND, stopper Excel med at kontrollere yderligere regler.
Forsinket med n dage fra i dag
Det kan være en god idé at sammenligne en forfaldsdato med datoen i dag. For at teste, om datoer er forsinket mindst n dage fra i dag, kan du bruge en formel som denne:
=(TODAY()-date)>=n
Denne formel returnerer kun SAND, når en dato er mindst n dage tidligere. Når en dato er i fremtiden, vil forskellen være et negativt tal, så reglen udløses aldrig.
For mere information om bygningsformelkriterier, se eksempler på 50+ formelkriterier.
