Formuła ogólna
=(date2-date1)>n
Podsumowanie
Aby wyróżnić „zaległe” daty, możesz użyć warunkowej reguły formatowania, która sprawdza, czy rozbieżność między dwiema datami jest większa niż określona liczba dni. W przedstawionym przykładzie trzy reguły formatowania warunkowego zostały zastosowane do zakresu D5: C12 z następującymi formułami:
=(D5-C5)<3 // rule 1 (green) =(D5-C5)=10 // rule 3 (red)
Uwaga: reguły formatowania warunkowego są oceniane względem lewej górnej komórki w zaznaczeniu w momencie tworzenia reguły, w tym przypadku D5.
Obliczanie wariancji w dniach
Odchylenia w kolumnie E oblicza się, odejmując pierwotną datę od daty bieżącej za pomocą następującego wzoru:
=D5-C5
Wynikiem jest różnica w dniach między pierwotną datą a datą bieżącą. Dodatnia różnica oznacza późną datę (tj. „Poślizg” w harmonogramie). Ujemna różnica oznacza, że bieżąca data wyprzedza harmonogram. To działa, ponieważ daty programu Excel są numerami seryjnymi.
Wariancja pokazana w kolumnie E służy wyłącznie jako odniesienie w tym przykładzie i nie jest używana przez reguły formatowania warunkowego. Jeśli jednak potraktujesz kolumnę E jako kolumnę pomocniczą, możesz napisać prostsze reguły formatowania warunkowego, które bezpośrednio używają wariancji.
Wyjaśnienie
W tym przykładzie chcemy zastosować trzy różne kolory, w zależności od tego, jak bardzo oryginalna data różni się od daty bieżącej:
- Zielony, jeśli rozbieżność jest mniejsza niż 3 dni
- Żółty, jeśli rozbieżność wynosi od 3 do 10 dni
- Czerwony, jeśli rozbieżność jest większa niż 10 dni
Dla każdej reguły obliczamy wariancję, odejmując pierwotną datę od „bieżącej” daty (jak wyjaśniono powyżej). Następnie sprawdzamy wynik za pomocą wyrażenia logicznego. Gdy wyrażenie zwraca wartość TRUE, wyzwalane jest formatowanie warunkowe.
Ponieważ chcemy mieć trzy oddzielne kolory, każdy z logicznym testem, będziemy potrzebować trzech oddzielnych reguł formatowania warunkowego. Poniższy ekran pokazuje, jak skonfigurowano reguły, aby zastosować formatowanie zielone, żółte i czerwone. Zauważ, że pierwsze dwie reguły mają zaznaczone „zatrzymaj, jeśli prawda”:
Reguły są oceniane w podanej kolejności. Reguła 1 sprawdza, czy wariancja jest mniejsza niż 3 dni. Reguła 2 sprawdza, czy rozbieżność jest mniejsza niż 10 dni. Reguła 3 sprawdza, czy rozbieżność jest większa lub równa 10 dni. Zarówno reguła 1, jak i reguła 2 mają włączoną opcję „Zatrzymaj, jeśli prawda”. Gdy którakolwiek z reguł zwróci wartość TRUE, program Excel przestanie sprawdzać dodatkowe reguły.
Przeterminowany o n dni od dzisiaj
Możesz porównać termin wykonania z datą dzisiejszą. Aby sprawdzić, czy daty są spóźnione o co najmniej n dni od dzisiaj, możesz użyć następującego wzoru:
=(TODAY()-date)>=n
Ta formuła zwróci wartość TRUE tylko wtedy, gdy data wypada co najmniej n dni wstecz. Kiedy data przypada w przyszłości, różnica będzie liczbą ujemną, więc reguła nigdy nie zadziała.
Aby uzyskać więcej informacji na temat tworzenia kryteriów formuł, zobacz ponad 50 przykładów kryteriów formuł.
