Как в excel подсветить просроченную дату
Перейти к содержимому

Как в excel подсветить просроченную дату

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

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

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

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

подсвечивание цветом ячеек с датами.

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

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

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

    Excel: Условное форматирование (часть 2)

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

    В этом случае в Окне Условное форматирование при указании условий необходимо вместо значения выбрать — «формула», а в соседней графе ввести формулу.

    Формат

    Если заданная формула верна, то срабатывает нужный формат.В этом случае можно задавать более сложные проверки с использованием функций и, кроме того, проверять одни ячейки, а форматировать — другие.

    Рассмотрим 2 примера.

    1. Выделение цветом всей строки

    Необходимо выделить цветом те строки таблицы, в которых в столбце «В» значения больше 5000.

    1.Выделите таблицу, к которой будет применяться Условное форматирование

    2. Выберите в меню Формат — Условное форматирование

    3. В открывшемся Окне задайте условие:

    • Выберите — «формула»
    • введите формулу =$B2>5000

    В2 — адрес первой ячейки столбца, в котором проверяются значения

    4. Выберите необходимый формат для выделения строки

    Формат

    Формат

    Теперь зеленым цветом будут выделены все строки, в которых в столбце «В» стоит значение большее 5000.

    Формат

    Главный нюанс заключается в знаке доллара ($) перед буквой столбца в адресе — он фиксирует столбец, оставляянезафиксированной ссылку на строку- проверяемые значения берутся из столбца указанного в формуле, по очереди из каждой последующей строки.

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

    Поскольку даты в Excel представляют собой те же числа (один день = 1), то можно легко использовать условное форматирование для проверки сроков оплат или выполнения задач.

    Например, необходимо выделить даты просроченных оплат красным цветом, а тех, что предстоят в ближайшую неделю – желтым.

    1. Выделите таблицу, к которой будет применяться Условное форматирование

    2. Выберите в меню Формат — Условное форматирование

    3. Задайте условие 1:

    • выберите — «формула»
    • введите формулу =B2 >» для ввода второго условия

    5. Задайте условие 2:

    • выберите — «формула»
    • введите формулу =(B2-$D$1)

    Добавить комментарий

    Ваш адрес email не будет опубликован.