Загальна формула
=(date2-date1)>n
Резюме
Щоб виділити дати, які «прострочені», ви можете використовувати правило умовного форматування, яке перевіряє, чи відхилення між двома датами перевищує певну кількість днів. У наведеному прикладі до діапазону D5: C12 застосовано три правила умовного форматування з цими формулами:
=(D5-C5)<3 // rule 1 (green) =(D5-C5)=10 // rule 3 (red)
Примітка: правила умовного форматування обчислюються відносно верхньої лівої комірки у виділенні під час створення правила, в даному випадку D5.
Розрахунок дисперсії в днях
Відхилення у стовпці E обчислюються шляхом віднімання вихідної дати від поточної дати за цією формулою:
=D5-C5
Результатом є різниця в днях між початковою датою та поточною датою. Позитивна різниця означає пізню дату (тобто «пробуксовку» в графіку). Негативна різниця означає, що поточна дата випереджає графік. Це працює, оскільки дати Excel - це серійні номери.
Дисперсія, показана у стовпці E, є лише довідковою в цьому прикладі і не використовується умовами правил умовного форматування. Однак якщо ви розглядаєте стовпець E як допоміжний стовпець, ви можете написати простіші правила умовного форматування, які безпосередньо використовують дисперсію.
Пояснення
У цьому прикладі ми хочемо застосувати три різні кольори, залежно від того, наскільки оригінальна дата відрізняється від поточної дати:
- Зелений, якщо дисперсія менше 3 днів
- Жовтий, якщо відхилення становить від 3 до 10 днів
- Червоний, якщо дисперсія перевищує 10 днів
Для кожного правила ми обчислюємо дисперсію, віднімаючи початкову дату від "поточної" дати (як пояснено вище). Потім перевіряємо результат логічним виразом. Коли вираз повертає TRUE, спрацьовує умовне форматування.
Оскільки нам потрібні три окремі кольори, кожен з яких має логічний тест, нам знадобляться три окремих правила умовного форматування. На екрані нижче показано, як правила були налаштовані для застосування зеленого, жовтого та червоного форматування. Зверніть увагу, що на перших двох правилах встановлено галочку "зупинити, якщо істина":
Правила оцінюються в наведеному порядку. Правила 1 тестує, якщо відхилення менше 3 днів. Правило 2 перевіряє, якщо відхилення менше 10 днів. Правило 3 перевіряє, чи відхилення більше або дорівнює 10 дням. І в правилі 1, і в правилі 2 увімкнено "зупинити, якщо істина". Коли будь-яке правило повертає TRUE, Excel припинить перевірку додаткових правил.
Прострочений на n днів від сьогодні
Можливо, ви захочете порівняти термін виконання із сьогоднішньою датою. Щоб перевірити, чи прострочені дати принаймні на n днів з сьогоднішнього дня, можна скористатися такою формулою:
=(TODAY()-date)>=n
Ця формула поверне ІСТИНУ лише тоді, коли дата минула щонайменше n днів. Коли дата в майбутньому, різниця буде від’ємним числом, тому правило ніколи не спрацює.
Для отримання додаткової інформації щодо побудови критеріїв формул див. Приклади 50+ критеріїв формул.
