Подсветка дат и сроков

Простой способ

Выделите диапазон с датами на листе и выберите на вкладке Главная – Условное форматирование – Правила выделения ячеек – Дата (Home – Conditional Formatting – Highlight Cell Rules – Date Occuring). В открывшемся окне выберите из выпадающего списка нужный вариант подсветки:

dates-in-color1.png

Сложный, но красивый способ

Теперь давайте разберем задачку посложнее и поинтереснее. Предположим, что у нас есть большая таблица поставок каких-либо товаров:

dates-in-color2.png

Обратите внимание на дату отгрузки. Если она в прошлом, то товар уже поставлен – можно не волноваться. Если она в будущем – значит мы должны держать вопрос на контроле и не забыть организовать поставку к указанному сроку. И, наконец, если дата отгрузки совпадает с сегодняшней, то надо бросать все дела и заниматься именно этой партией в данный момент (наивысший приоритет).

Для наглядности, можно настроить три правила условного форматирования, чтобы автоматически заливать всю строку с данными по партии в разные цвета в зависимости от даты отгрузки. Для этого выделим всю таблицу (без шапки) и выберем на вкладке Главная – Условное форматирование – Создать правило (Home – Conditional Formatting – Create Rule). В открывшемся окне зададим последний тип правила Использовать формулу для определения форматируемых ячеек (Use formula to determine which cell to format) и введем в поле следующую формулу:

dates-in-color3.png

Эта формула берет последовательно содержимое ячеек E5, E6, E7… из столбца с датой отгрузки и сравнивает эту дату с сегодняшней датой из ячейки C2. Если дата отгрузки раньше, чем сегодняшняя, то отгрузка уже была. Обратите внимание на знаки доллара, используемые для закрепления ссылок. Ссылка на $C$2 должна быть абсолютной – с двумя знаками доллара. Ссылка на первую ячейку столбца с датой отгрузки должна быть с закреплением только столбца, но не строки, т.е. $E5.

После ввода формулы можно задать цвет заливки и шрифта, нажав на кнопку Формат (Format) и применить потом наше правило, нажав на кнопку ОК. Затем повторить всю процедуру для проверки будущих поставок и поставок на текущий день. Для отгруженных партий можно, например, выбрать серый цвет, для будущих заказов – зеленый и для сегодняшних – срочный красный:

dates-in-color3.png

Вместо текущей даты можно вставить в ячейку С2 функцию СЕГОДНЯ (TODAY), которая будет обновлять дату каждый раз при открытии файла, что будет приводить к автоматическому обновлению цветов в таблице.

Если подобная подсветка нужна не всегда, а только на определенное время работы с таблицей, то можно добавить к уже сделанному еще и, своего рода, выключатель. Для этого откройте вкладку Разработчик (Developer). Если ее не видно, то сначала включите ее через Файл – Параметры – Настроить ленту и нажмите кнопку Вставить (Insert):

dates-in-color5.png

В открывшемся списке инструментов выберите Флажок (Checkbox) из верхнего набора Элементы управления формы и щелкните по тому месту листа, где хотите его разместить. Затем можно задать размеры надписи и поменять ее текст (правой кнопкой мыши – Изменить текст):

dates-in-color6.png

Теперь, чтобы использовать флажок для включения-выключения подсветки, нужно связать его с любой ячейкой на листе. Щелкните правой кнопкой мыши по нарисованному флажку и выберите в контекстном меню команду Формат объекта (Format Object) и затем в открывшемся окне задайте любую подходящую ячейку в поле Связь с ячейкой (Cell Link):

dates-in-color7.png

Проверьте как все работает. В связанную ячейку Е2 должно выводиться значение ИСТИНА, когда флажок включен или ЛОЖЬ, когда он выключен.

Теперь осталось добавить одно правило в условное форматирование, чтобы наш флажок включал-выключал подсветку дат. Выделите всю нашу таблицу (кроме шапки) и откройте на вкладке Главная - Условное форматирование - Управление правилами (Home - Conditional Formatting - Manage Rules). В открывшемся окне должны быть хорошо видны созданные нами ранее правила для подсветки прошлых, будущих и настоящих дат разными цветами:

dates-in-color8.png

Жмем кнопку Создать правило (New Rule), выбираем последний тип правила Использовать формулу для определения форматируемых ячеек (Use formula to determine which cell to format) и вводим в поле следующую формулу:

dates-in-color9.png

Формат не задаем и нажимаем ОК. Созданное правило должно добавится к общему списку. Теперь необходимо поднять его на первую строчку стрелками (если оно не еще не там) и включить напротив него справа флажок Остановить если истина (Stop If True):

dates-in-color10.png

Параметр с малопонятным названием Остановить, если истина делает простую вещь: если правило, напротив которого он стоит, выполняется (т.е. наш флаг Подсветка сроков на листе выключен), то Microsoft Excel останавливает дальнейшую обработку правил, т.е. не переходит к следующим правилам в списке условного форматирования и не заливает таблицу. Что и требуется.

Ссылки по теме



Браво автору!
Николай, подскажите пожалуйста. На примере заливки "будущих отгрузок" - те, что зеленым цветом,
Как можно задействовать "денежные суммы", именно в выделеных строках, к примеру суммировать выделенные "затраты" или суммировать "общую массу" в отдельной ячейке?
19.04.2014 11:15:47
Использовать функцию =СУММЕСЛИ(E5:E23; ">"&C2; D5:D23)
15.07.2014 09:49:06
Добрый день!
Николай скажите, можно ли справа от таблицы сделать 4 кнопки, отдельно каждая, чтобы при нажатии на нее можно было показывать только красные строки, нажав на синюю кнопку только синюю и т.д.?  Чтобы не открывать фильтр и там выбирать фильтр по цвету. Свести все управление к вставленным кнопкам. И еще одна кнопка чтоб фильтровать даты по очереди, т.к. в таблице они записаны все в разнобой.
15.07.2014 16:39:12
Можно, но такое только макросом.
15.07.2014 20:25:05
Ох жалко. Думал с помощью стандартных опции экселя справлюсь.
А макрос большой может получиться?  
18.11.2014 17:04:33
Николай, спасибо за подсказки! Есть вопрос: можно ли при назначении условного форматирования дат, дату, которая предшествует "сегодня" на два-три дня выделить желтым, а даты, которые далеко в будущем (от трех дней и далее) зеленым, например?
06.03.2015 07:58:34
Добрый день!
Подскажите пожалуйста, можно ли установить форматирование строки по условию в одной ячейке? Например, если в ячейке "сп", то строка "бледно-оранжевая", если "зп", то строка серая, если "п", то строка розовая и т.д. И так по всей таблице.
19.07.2015 10:52:48
Сложный, но красивый способ, описанный в статье, так именно и работает - посмотрите его повнимательнее.
17.07.2015 02:47:28
доброго времени суток.
вопрос по красивому способу. есть бланк заявок в котором даты окончание каждого этапа производства рекламы (заявка/макет/согласование/утверждение/изготовление/установка). так вот хотелось чтоб например если уже неделю по заявке ничего не происходит, то отметить желтым. две недели - оранжевым. месяц - красным. как это реализовать?
19.07.2015 10:51:16
Не видя файла сказать что-то определенное сложно, но:
  1. я бы искал с помощью функции МАКС самую последнюю дату активности по проекту
  2. проверял бы вычитанием СЕГОДНЯ-МАКС сколько дней прошло с момента последней активности
  3. если это количество больше 14 - ставил оранжевый, если больше 30 - красный и т.д. с помощью условного форматирования.
Как-то так :)
03.11.2015 16:38:59
Спасибо, супер!
25.12.2015 22:55:39
Здравствуйте!
Николай, подскажите, пожалуйста, будет ли работать подсветка дат и сроков, если таблица будет "умной" (динамической)?
22.04.2017 10:57:18
Да, конечно.
А если есть правило и таблица, но в некоторых ячейках даты еще нет, она все равно окрашивается, как просроченная, как это исправить. (получается, что пустая ячейка меньше сегодняшней даты)
22.04.2017 10:59:14
Добавить еще одно условие со связкой И (AND) для проверки на пустоту. Что-то типа:
=И(НЕ(ЕПУСТО(A1));A1<СЕГОДНЯ()) 
22.03.2017 13:08:34
Добрый день! подскажите, пожалуйста, кто знает! Задача следующая! У меня есть список сотрудников! Под каждим стоит дата Его рождения в формате "05 ноября 1968 г."! Вопрос: как сделать, чтоб при приближении даты рождения на неделю, на текущей неделе, завтра, сегодня, вчера - ячейка выделялась! Способом выделения дата - сегодня и т.д. не выделяет...так как год не совпадает! Обыскал интернет...подобного не нашел! Спасибо всем, кто откликнется!
30.03.2017 16:47:16
а если выделять из ДР отдельно день и месяц? А потом сравнивать его с "СЕГОДНЯ"?
04.05.2017 19:39:48
Сколько раз обращался с вопросами по екселю в гугл - ответ находил у Николая. Спасибо за мои сохраненные время и деньги!
19.05.2017 09:14:08
Добрый день! Мне нужно подсветить ячейки для которых выполняется условие "дата < определенная дата в следующем году" (или хотя бы конец текущего года). Форматирование Excel 2010 содержит диапазон только "прошлый месяц - следующий месяц". Как быть?
Добрый день!

Подскажите пожалуйста, возможно ли задать условие для форматирования вида =$F$4<($C$1+1),
где $C$1- это соответственно сегодняшняя дата,
$F$4 - текущая ячейка с предполагаемой датой отгрузки.
Необходимо сделать так, чтобы интервал от 3-5 дней до отгрузки от текущей даты, подсвечивался определенным цветом для отслеживания этапа отгрузки (второй, сложный тип). Соответственно, только ли создавая каждый раз условие можно добиться нужного эффекта или же можно задать интервал в условии (или же двойное условие).
24.10.2017 17:21:51
Добрый день!

Честно говоря не понял как сделать написанное тут:
Затем повторить всю процедуру для проверки будущих поставок и поставок на текущий день. Для отгруженных партий можно, например, выбрать серый цвет, для будущих заказов – зеленый и для сегодняшних – срочный красный:
Как сделать это "повторить"? Ведь мы ввели формулу
=$E5<$C$2

т.е. условие -- "просто меньше". "На сколько меньше" (на день, два, три, семь...) это условие сведений не содержит. Как тогда "повторить всю процедуру"? Несколько раз с одной и той же формулой, но разным форматированием? Не понимаю...
02.11.2017 22:37:52
Николай , вы облегчили мою работу на 99%!!! И настолько доходчиво и доступным языком !
05.03.2018 17:32:56
Spasibo Nikolay.
Ishu ne mogu nayti otvet vot na kakoy vopros.
Vverxu kajdogo stolbika oboznachena data. I tak na 1 god. Sprava 2 stolbsa nedvigayushiyesya.
Kak sdelat chtob kogda otkrivat tablisu kajdiy den avtomaticheski otkrivalya stolbik s tekushey datoy. Spasibo.
Pojaluysta otvette.
07.05.2018 19:58:17
Здравствуйте, прошу помощи в создании формулы, в сложном варианте данной таблицы, чтобы завтрашний день от нынешнего дня подсвечивался другим цветом, а остальные оставались зелёными, пожалуйста подскажите
22.05.2018 11:55:38
Добрый день Николай!
Подскажите пожалуйста, как создать форматирования по времени. По часам то есть.
29.06.2018 08:31:09
Здравствуйте! Подсветка работает только тогда, когда входишь в ячейку двойным нажатием кнопки мыши. А автоматически не хочет... Что нужно сделать????
26.01.2019 15:58:15
Здравствуйте. Спасибо вам большое за классный способ составить вечный календарь!

У меня вопрос: Как сделать подстветку нескольких ячеек по вертикали, синхронизировать их, чтобы они закрашивались в соответствующей ячейке? Я ввожу в условное форматирование такую формулу "=C4=$AJ$4:$AJ$32", между AJ4:AJ32 у меня даты, но проблема в том, что подсвечивается только AJ4, остальные не срабатывают. В чём может быть проблема?

ссылка на документ
Наверх