Как выбрать дату на текущий месяц во всех ячейках таблицы Excel

Допустим поставки товаров от поставщика 1 раз в неделю. Банковские транзакции взаиморасчетов с поставщиком товаров производятся регулярно каждый вторник. Необходимо быстро проверить в какие дни дата поставки товара совпадает с датой взаиморасчетов. Это позволит предоставить ежемесячный отчет за прошлый месяц о взаиморасчетах поставщику в день поставки товара и сэкономить на курьерских услугах. Для этого следует выделить все даты предполагаемых поставок, припадающие на вторник.

Поиск даты в диапазоне дат Excel

Для решения выше описанной задачи мы, используя специальную формулу в условном форматировании выполним поиск ближайшей даты в Excel для вторников и выделим цветом эти ячейки. Автоматическое выделения цветом календарных дат:

Даты доставки.
  1. Выделите диапазон ячеек B2:B10 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
    2. Создание правила.
  2. Выберите: «Использовать формулу для определения форматируемых ячеек».
    3. ДЕНЬНЕД.
  3. Чтобы выбрать дни вторников в Excel для поля ввода введите формулу: =ДЕНЬНЕД(B2;2)=2 и нажмите на кнопку «Формат», чтобы задать желаемый цвет заливки для ячеек. Например, зеленый. Нажмите ОК на всех открытых окнах.
Зеленый.

Результат выделения цветом каждого вторника в списке дат поставок товаров:

выделение цветом каждого вторника.

В формуле мы использовали функцию =ДЕНЬНЕД(), которая возвращает номер дня недели для исходной даты. В первом аргументе функции указываем ссылку на исходную дату. Ссылка относительная так как будет проверятся каждая дата в столбце B. Во втором аргументе обязательно следует указать число 2, чтобы функция правильно выдавала нам очередности дней недели (1-понедельник, 2-вторник и т.д.). Если не указать второй аргумент, то функция будет считать дни в английском формате: 1-sanday (неделя), 2-Monday (понедельник) и т.д.

Дальше идет оператор сравнения с числом 2. Если возвращаемое число функцией = 2 значит формула возвращает значение ИСТИНА и к текущей ячейке применяется пользовательский формат (красная заливка).



Выделения дат цветом только текущего месяца

В таблице представлен список проектов, которые должны быть реализованные на протяжении текущего полугодия. Список большой, но нас интересуют только те проекты, которые должны быть реализованы в текущем месяце. Визуально искать в списке даты текущего месяца – весьма неудобно. Можно использовать условное форматирование, чтобы Excel нам автоматически выделил цветом интересующие нас даты в списке. Пример таблицы проектов на текущий месяц в Excel:

список проектов.

Нам необходимо из целой таблицы выбрать дату в Excel на текущий месяц:

  1. Выделите диапазон ячеек B2:B11 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
    2. Создать правило.
  2. Снова выбираем: «Использовать формулу для определения форматируемых ячеек».
    3. МЕСЯЦ.
  3. В поле ввода введите формулу: =МЕСЯЦ(B2)=МЕСЯЦ($C$2) и нажмите на кнопку «Формат», чтобы задать желаемый цвет заливки для ячеек. Например, зеленый. Нажмите ОК на всех открытых окнах.
Зеленый.

В результате визуально отчетливо видно дни текущего месяца:

дни текущего месяца.

В представленной формуле для условного форматирования главную роль играет функция =МЕСЯЦ(), которая определяет номер месяца исходной даты. Ссылка на ячейку в аргументе первого раза использования функции МЕСЯЦ – относительная, так как проверятся будут несколько ячеек в столбце C. После функции стоит оператор сравнения с номером текущего месяца. Там уже используется абсолютная ссылка (значение аргумента неизменяемое). Если результаты вычислений по обе стороны оператора сравнения совпадают, тогда формула возвращает значение ИСТИНА и к текущей ячейке присваивается пользовательский формат (заливка зеленым цветом).

Теперь усложним задачу. Допустим у нас список проектов охватывает не полугодие, а несколько лет:

список проектов на несколько лет.

Тогда возникает сложность ведь в разные года номера месяцев будут совпадать и условное форматирование будет выделять лишние даты. Поэтому необходимо модернизировать формулу:

ГОД.

Готово:

Пример.

Мы добавили в формулу две функции, которые позволяют нам выбрать дни определенного месяца и года:

  1. =ГОД() – функция работает по тому же принципу что и =МЕСЯЦ(). Разница только в том, что возвращает из даты номер года.
  2. =И() – данное имя функции говорит само за себя. То есть должны совпадать и номера месяцев и их номера годов.

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

Читайте так же: как посчитать разницу между датами в днях.


en ru