Формула Excel: список праздников между двумя датами

Есть таблица праздников с соответствующими датами. Как вывести все праздники, попадающие в заданный диапазон дат в Excel? Ниже приведена формула массива для решения этой задачи.

Если вы хотите повторить действия из этого руководства, загрузите пример файла.

Универсальная формула

Синтаксис и аргументы

Возвращаемое значение

Если в указанном диапазоне дат праздники не найдены, формула возвращает пустую ячейку; в противном случае — соответствующие праздничные дни.

Примечание

Функция СЦЕПИТЬТЕКСТ доступна исключительно в Office 365, Excel 2019 и более поздних версиях.

Как работает эта формула

Структура данных следующая:
B3:B6 — список праздников;
C3:C6 — даты праздников;
F2 — дата начала;
F3 — конечная дата;
E4 — список праздников между датой начала и конечной датой.

Используйте формулу следующим образом:

Удерживайте клавиши Shift и Ctrl, чтобы получить корректный результат.

Пояснение

Функция ЕСЛИ: проверяет условие и возвращает ИСТИНА или ЛОЖЬ, а также позволяет задать собственные текстовые значения вместо стандартных результатов.

Функция СЦЕПИТЬТЕКСТ: объединяет два или более значения с помощью разделителей.

Формулу можно разбить на три части:

{IF(C3:C6<=F3,$B$3:$B$6,«»)} определяет, какие праздничные даты меньше или равны дате окончания. В результате получится следующий массив: {«Новый год»;«День независимости»;«День благодарения»;«»}.

{IF(C3:C6>=F2,IF(C3:C6<,=F3,$B$3:$B$6,«»),«»)}, согласно приведённой выше формуле, эту часть можно представить так:

{IF(C3:C6>=F2,{«New Year»;«Independence Day»;«Tanksgiving»;«»},«»)}: определяет, какие праздничные даты больше или равны дате начала. В результате получится следующий массив: {«»;«День независимости»;«День благодарения»;«»}.

{СЦЕПИТЬТЕКСТ(", ",ИСТИНА,IF(C3:C6>=F2,IF(C3:C6<,=F3,$B$3:$B$6,«»),«»))}, теперь вся формула может быть представлена следующим образом:

{СЦЕПИТЬТЕКСТ(", ", ИСТИНА, {«»;«День независимости»;«День благодарения»;«»})} — объединяет значения через запятую, пропуская пустые элементы.

Связанные формулы

• Проверка, относится ли дата к последним N месяцам от текущей
  Допустим, вам нужно определить, попадает ли дата приёма сотрудника в последние 6 месяцев от текущей даты. Для этого используйте формулу в Excel, сочетающую функции И, КОНМЕСЯЦА и СЕГОДНЯ — и задача будет решена мгновенно!
• Проверка, является ли дата последним N днём от текущей
  Чтобы проверить, наступает ли сегодня срок годности продукции, используйте формулу, определяющую, приходится ли дата производства на 7 дней назад от текущей даты.
• Преобразование названия месяца в число
  В этом руководстве вы найдёте формулу, которая преобразует название месяца в соответствующий номер месяца в Excel.
• Получение той же даты в прошлом или следующем году
  В этом руководстве представлены две формулы для получения той же даты в прошлом и в следующем году относительно заданной даты в Excel.

Связанные функции

• Функция ДАТА в Excel
  Создание даты из года, месяца и дня
• Функция ГОД в Excel
  Возвращает год даты в виде четырёхзначного серийного номера
• Функция МЕСЯЦ в Excel
  Функция МЕСЯЦ используется для получения месяца в виде целого числа (1–12) из даты
• Функция ДЕНЬ в Excel
  Функция ДЕНЬ возвращает день в виде числа (1–31) из даты
• Функция РАБДЕНЬ в Excel
  Добавляет рабочие дни к указанной дате и возвращает рабочий день