Как в excel сделать напоминание по дате

Создаем «напоминалку» в Excel

Когда вы работаете с таблицей, содержащей даты, которые нужно не пропустить при их наступлении, неизбежно возникает вопрос о том, как это гарантированно сделать. В Excel можно использовать различные средства для создания “напоминалки”. Прочитав эту статью, вы узнаете о них.

Для наглядности возьмем совсем несложный пример. Предположим, в некоторой таблице отмечается дата регистрации заказа и дата, когда он должен быть выполнен. За сутки до даты выполнения необходимо перезвонить клиенту. Какие варианты “напоминалок” можно использовать?

Во-первых, можно таблицу раскрасить. Например, строки, относящиеся к заказу, по поводу которого пора звонить, должны окрашиваться красным. Строки заказов, срок исполнения которых отстоит от сегодняшней даты более чем на 1 день, должны быть окрашены голубым. Строки заказов, срок исполнения которых уже прошел, вообще не окрашиваются. Последовательность действий для реализации этой “задумки”:

выделите столбцы таблицы (можно целиком, чтобы не ограничивать количество строк с данными);
в меню команды Условное форматирование (вкладка Главная) выберите строку Управление правилами;
создайте правило в виде формул: “=$C2-СЕГОДНЯ()>1” (формат: заливка голубым) и “=$C2-СЕГОДНЯ()=1” (формат: заливка красным);
примените созданные правила.

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

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

Читать еще: Как сделать багажник на крышу своими руками

вставьте дополнительный столбец, например, перед таблицей;
в первую ячейку столбца проверки вставьте формулу: “=ЕСЛИ(D2-СЕГОДНЯ()=1;”Позвонить”;” “)“;

скопируйте ее в остальные ячейки столбца;
установите фильтр (вкладка Данные, кнопка Фильтр);
используйте фильтр для столбца Напоминание.

После того, как всем позвонили, снимите фильтр со столбца Напоминание и продолжайте ввод данных о новых заказах.

В-третьих, можно формировать список звонков с помощью расширенного фильтра.

Плюсом этого решения является создание отдельного списка напоминаний. Минусом — необходимость удаления сформированного списка перед формированием нового на следующий день. Но эту проблему можно решить, создав макрос.

В статье было предложено три варианта создания “напоминалки”. Конечно, возможны и другие, более сложные варианты. Но даже рассмотренные приемы существенно облегчат вашу ежедневную работу.

Пример формулы для выделения цветом просроченных дат в Excel

Во многих фирмах отдельное внимание уделяется датам, выпадающим после определенного пройденного периода. С помощью условного форматирования можно легко составить отчет «После периода» на котором выделены пройденные даты.

Как сделать подсвечивание цветом ячеек с датами пройденного срока в Excel

Пример представлен ниже на рисунке в виде отчета, в котором даты за более чем 90 дней от текущей даты выделенные другим цветом заливки.

Чтобы составить аналогичный отчет с таким же автоматическим форматированием ячеек по условию выполните следующее:

Выделите целевой диапазон ячеек (в данном примере A3:A8) и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». В результате чего появится окно для внесения всех необходимых настроек инструмента:
В появившемся окне из верхней части где находится список опций выберите пункт: «Использовать формулу для определения форматируемых ячеек». Данная опция позволяет нам использовать собственные формулы для составления сложны правил условного форматирования. Формула должна содержать логическое выражение и соответственно возвращать логическое значение для каждой ячейки из выделенного диапазона. Если будет возвращено – ИНСТИНА, тогда к этой ячейке будет применятся правило и присваивается новый формат, который предварительно настроен этим же инструментом.
В полю ввода формул введите логическое выражение представленное на этом шаге. Данная формула проверяет значение ячеек: будет ли их дата выпадать после 90 дней, пройденных от сегодняшнего дня. Отсчитывается от даты, указанной в целевой ячейке A3 выделенного просматриваемого диапазона. Если да (ИСТИНА) – сразу же применяется условное форматирование.

Читать еще: Как сделать антенну для самолетов 1 144

Нажмите на кнопку «Формат» для вызова окна, в которому будут доступные все опции оформления формата: цвет фона и границы, размер шрифта и т.п. После указания желаемых настроек для оформления стиля форматирования нажмите кнопку ОК на всех открытых окнах, чтобы подтвердить все настройки и получить готовый результат.

А в результате выделились все даты актуальность которых превышает 90 дней.

Информирование пользователя MS EXCEL о принадлежности ДАТЫ к определенному диапазону

Используем Условное форматирование для подачи сигнала пользователю MS EXCEL о принадлежности даты к определенному диапазону.

Предположим, что пользователь вводит некие даты событий. Требуется, чтобы EXCEL автоматически выделял ячейки следующим образом:

красным, если дата совпадает с сегодняшним днем;
оранжевым, если вводимая дата находится в пределах 1 недели от сегодняшнего числа;
зеленым, если вводимая дата находится в пределах 2 недель от сегодняшнего числа.

Сначала создадим правило, по которому ячейка будет выделяться, если дата совпадает с сегодняшним днем (см. Файл примера ):

выделите диапазон, в который пользователь будет вводить данные;
вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Управление правилами ). Откроется окно Диспетчер правил условного форматирования;
нажмите Создать правило;
выберите Форматировать только ячейки, которые содержат;
в выпадающем списке выберите Равно;
введите формулу =СЕГОДНЯ() ;
нажав кнопку Формат выберите, например, красный шрифт;

нажмите ОК и вернитесь в Диспетчер правил условного форматирования;

Теперь создадим правило, по которому ячейка будет выделяться, если дата находится в пределах 1 недели от сегодняшнего числа:

нажмите Создать правило;
выберите Форматировать только ячейки, которые содержат;
в выпадающем списке выберите Между;
введите формулы =СЕГОДНЯ()-7 и =СЕГОДНЯ()+7 ;
нажав кнопку Формат выберите, например, оранжевый шрифт;

Проделайте аналогичные шаги для создания правила, по которому ячейка будет выделяться если дата находится в пределах двух недель от сегодняшнего числа, используйте формулы =СЕГОДНЯ()-14 и =СЕГОДНЯ()+14 .

Читать еще: Как сделать булочки розочки из теста

ВНИМАНИЕ!
Когда к диапазону ячеек применяются два или более правил Условного форматирования, приоритет обработки определяется порядком их перечисления в Диспетчере правил условного форматирования. Правило, расположенное в списке выше, имеет более высокий приоритет, чем правило, расположенное в списке ниже. Новые правила всегда добавляются в начало списка и поэтому обладают более высоким приоритетом, однако порядок правил можно изменить в диалоговом окне при помощи кнопок со стрелками Вверх и Вниз.

Если мы расположим правила как показано на предыдущем рисунке, то при вводе сегодняшней даты она выделится зеленым цветом, а не красным, т.к. правило (Между =СЕГОДНЯ()-14 и =СЕГОДНЯ()+14 ) у нас идет первым и имеет наивысший приоритет. Для правильного отображения поменяем порядок критериев, используя соответствующие стрелочки.

В результате получим вот такую картину.

СОВЕТ:
Чтобы найти все ячейки на листе, к которым применены правила Условного форматирования необходимо:

на вкладке Главная в группе Редактирование щелкните стрелку рядом с командой Найти и выделить;
выберите в списке пункт Условное форматирование;
будут выделены все ячейки, которым применены правила Условного форматирования.

Источники:

http://microsoft-help.ru/47-sozdaem-napominalku-v-excel.html

http://exceltable.com/formuly/vydelenie-prosrochennyh-dat

http://excel2.ru/articles/informirovanie-polzovatelya-ms-excel-o-prinadlezhnosti-daty-k-opredelennomu-diapazonu