Generisk formel
=(date2-date1)>n
Sammendrag
For å markere datoer som er "forfalt", kan du bruke en betinget formateringsregel som sjekker om avviket mellom to datoer er større enn et bestemt antall dager. I eksemplet som er vist, har tre betingede formateringsregler blitt brukt på området D5: C12 med disse formlene:
=(D5-C5)<3 // rule 1 (green) =(D5-C5)=10 // rule 3 (red)
Merk: betingede formateringsregler evalueres i forhold til den øverste venstre cellen i utvalget på det tidspunktet regelen ble opprettet, i dette tilfellet D5.
Beregner avvik i dager
Avvikene i kolonne E beregnes ved å trekke den opprinnelige datoen fra gjeldende dato med denne formelen:
=D5-C5
Resultatet er forskjellen i dager mellom den opprinnelige datoen og den gjeldende datoen. En positiv forskjell representerer en sen dato (dvs. en "slip" i timeplanen). En negativ forskjell indikerer at dagens dato er før planen. Dette fungerer, fordi Excel-datoer er serienumre.
Avviket vist i kolonne E er kun for referanse i dette eksemplet, og brukes ikke av betingede formateringsregler. Imidlertid, hvis du behandler kolonne E som en hjelpekolonne, kan du skrive enklere betingede formateringsregler som bruker variansen direkte.
Forklaring
I dette eksemplet ønsker vi å bruke tre forskjellige farger, avhengig av hvor mye den opprinnelige datoen varierer fra den gjeldende datoen:
- Grønn hvis avviket er mindre enn 3 dager
- Gul hvis avviket er mellom 3 og 10 dager
- Rød hvis avviket er større enn 10 dager
For hver regel beregner vi en varians ved å trekke den opprinnelige datoen fra den "nåværende" datoen (som forklart ovenfor). Så sjekker vi resultatet med et logisk uttrykk. Når et uttrykk returnerer SANT, blir den betingede formateringen utløst.
Fordi vi vil ha tre separate farger, hver med en logisk test, trenger vi tre separate betingede formateringsregler. Skjermen nedenfor viser hvordan reglene er konfigurert til å bruke den grønne, gule og røde formateringen. Legg merke til at de to første reglene har merket "stop if true":
Reglene evalueres i den viste rekkefølgen. Regel 1 tester om avviket er mindre enn 3 dager. Regel 2 sjekker om avviket er mindre enn 10 dager. Regel 3 sjekker om avviket er større enn eller lik 10 dager. Både regel 1 og regel 2 har "stopp hvis sant" aktivert. Når en av reglene returnerer SANT, slutter Excel å sjekke flere regler.
Forfalt med n dager fra i dag
Det kan være lurt å sammenligne en forfallsdato med dagens dato. For å teste om datoene er forsinket minst n dager fra i dag, kan du bruke en formel som denne:
=(TODAY()-date)>=n
Denne formelen vil bare returnere SANT når en dato er minst n dager tidligere. Når en dato er i fremtiden, vil forskjellen være et negativt tall, så regelen vil aldri avfyres.
For mer informasjon om kriterier for byggformel, se eksempler på 50+ formelkriterier.
