Všeobecný vzorec
=(date2-date1)>n
Zhrnutie
Na zvýraznenie dátumov, ktoré sú po termíne splatnosti, môžete použiť pravidlo podmieneného formátovania, ktoré kontroluje, či je rozptyl medzi dvoma dátumami väčší ako určitý počet dní. V zobrazenom príklade boli na rozsah D5: C12 aplikované tri pravidlá podmieneného formátovania s týmito vzorcami:
=(D5-C5)<3 // rule 1 (green) =(D5-C5)=10 // rule 3 (red)
Poznámka: Pravidlá podmieneného formátovania sa hodnotia vzhľadom na ľavú hornú bunku vo výbere v čase vytvorenia pravidla, v tomto prípade D5.
Výpočet odchýlky v dňoch
Odchýlky v stĺpci E sa vypočítajú odpočítaním pôvodného dátumu od aktuálneho dátumu pomocou tohto vzorca:
=D5-C5
Výsledkom je rozdiel v dňoch medzi pôvodným dátumom a aktuálnym dátumom. Pozitívny rozdiel predstavuje neskorý dátum (tj „sklz“ v rozvrhu). Záporný rozdiel naznačuje, že aktuálny dátum je pred plánovaným termínom. To funguje, pretože dátumy programu Excel sú sériové čísla.
Odchýlka zobrazená v stĺpci E slúži len na porovnanie v tomto príklade a pravidlá pre podmienené formátovanie ju nepoužívajú. Ak však so stĺpcom E zaobchádzate ako so stĺpcom pomocníka, môžete napísať jednoduchšie pravidlá podmieneného formátovania, ktoré priamo používajú odchýlku.
Vysvetlenie
V tomto príklade chceme použiť tri rôzne farby v závislosti od toho, ako veľmi sa líši pôvodný dátum od aktuálneho dátumu:
- Zelená, ak je odchýlka menšia ako 3 dni
- Žltá, ak je odchýlka medzi 3 a 10 dňami
- Červená, ak je odchýlka väčšia ako 10 dní
Pre každé pravidlo vypočítame odchýlku odčítaním pôvodného dátumu od „aktuálneho“ dátumu (ako je vysvetlené vyššie). Potom výsledok skontrolujeme logickým výrazom. Keď výraz vráti hodnotu TRUE, spustí sa podmienené formátovanie.
Pretože chceme tri samostatné farby, každá s logickým testom, budeme potrebovať tri samostatné pravidlá podmieneného formátovania. Na obrazovke nižšie je zobrazené, ako boli pravidlá nakonfigurované na použitie zeleného, žltého a červeného formátovania. Všimnite si, že prvé dve pravidlá majú začiarknuté „stop if true“:
Pravidlá sa vyhodnocujú v uvedenom poradí. Pravidlo 1 sa testuje, ak je odchýlka kratšia ako 3 dni. Pravidlo 2 kontroluje, či je odchýlka menšia ako 10 dní. Pravidlo 3 kontroluje, či je rozptyl väčší alebo rovný 10 dňom. Pravidlo 1 aj pravidlo 2 majú povolené „stop if true“. Keď jedno z pravidiel vráti hodnotu PRAVDA, Excel prestane kontrolovať ďalšie pravidlá.
Po dnešku oneskorené o n dní
Možno budete chcieť porovnať termín splatnosti s dnešným dátumom. Ak chcete otestovať, či sú dátumy oneskorené o najmenej n dní od dnešného dňa, môžete použiť nasledujúci vzorec:
=(TODAY()-date)>=n
Tento vzorec vráti hodnotu PRAVDA, iba ak je dátum v minulosti aspoň n dní. Ak bude dátum v budúcnosti, rozdiel bude záporné číslo, takže pravidlo sa nikdy nespustí.
Ďalšie informácie o zostavovaní kritérií vzorca nájdete v príkladoch viac ako 50 vzorcových kritérií.
