Generisk formel
=(date2-date1)>n
Sammanfattning
För att markera datum som är "förfallna" kan du använda en villkorlig formateringsregel som kontrollerar om variansen mellan två datum är större än ett visst antal dagar. I exemplet som visas har tre villkorliga formateringsregler tillämpats på intervallet D5: C12 med följande formler:
=(D5-C5)<3 // rule 1 (green) =(D5-C5)=10 // rule 3 (red)
Obs! Villkorliga formateringsregler utvärderas i förhållande till den övre vänstra cellen i markeringen vid den tidpunkt då regeln skapas, i det här fallet D5.
Beräkning av varians i dagar
Avvikelserna i kolumn E beräknas genom att subtrahera det ursprungliga datumet från det aktuella datumet med denna formel:
=D5-C5
Resultatet är skillnaden i dagar mellan det ursprungliga datumet och det aktuella datumet. En positiv skillnad representerar ett sent datum (dvs. en "slip" i schemat). En negativ skillnad indikerar att det aktuella datumet ligger före schemat. Detta fungerar eftersom Excel-datum är serienummer.
Variansen som visas i kolumn E är endast för referens i detta exempel och används inte av reglerna för villkorlig formatering. Men om du behandlar kolumn E som en hjälpkolumn kan du skriva enklare villkorliga formateringsregler som använder variansen direkt.
Förklaring
I det här exemplet vill vi använda tre olika färger, beroende på hur mycket originaldatumet varierar från det aktuella datumet:
- Grön om variansen är mindre än 3 dagar
- Gul om avvikelsen är mellan 3 och 10 dagar
- Röd om variansen är större än 10 dagar
För varje regel beräknar vi en varians genom att subtrahera det ursprungliga datumet från det "aktuella" datumet (som förklaras ovan). Sedan kontrollerar vi resultatet med ett logiskt uttryck. När ett uttryck returnerar SANT utlöses den villkorliga formateringen.
Eftersom vi vill ha tre separata färger, var och en med ett logiskt test, behöver vi tre separata villkorliga formateringsregler. Skärmen nedan visar hur reglerna har konfigurerats för att tillämpa den gröna, gula och röda formateringen. Observera att de två första reglerna har kryssat av "stop if true":
Regler utvärderas i den ordning som visas. Regel 1 testar om variansen är mindre än tre dagar. Regel 2 kontrollerar om variansen är mindre än tio dagar. Regel 3 kontrollerar om variansen är större än eller lika med tio dagar. Både regel 1 och regel 2 har "stop if true" aktiverat. När någon av reglerna returnerar SANT slutar Excel att kontrollera ytterligare regler.
Försenad med n dagar från idag
Du kanske vill jämföra ett förfallodatum med dagens datum. För att testa om datum är försenade med minst n dagar från idag kan du använda en sådan formel:
=(TODAY()-date)>=n
Denna formel returnerar SANT endast när ett datum är minst n dagar tidigare. När ett datum är i framtiden kommer skillnaden att vara ett negativt tal, så regeln kommer aldrig att avfyras.
Mer information om byggformelkriterier finns i 50+ exempel på formelkriterier.
