Как вычислить дату праздника в Excel

В этом примере мы расскажем, как вычислить в Excel дату праздника для любого года.

Прежде, чем мы начнем, давайте заострим внимание на 2-х моментах:

• Функция CHOOSE (ВЫБОР) возвращает значение, зависящее от порядкового номера позиции. Например: =CHOOSE(3,»Car»,»Train»,»Boat»,»Plane») возвращает «Boat».
• Функция WEEKDAY (ДЕНЬНЕД) возвращает число от 1 (воскресенье) до 7 (суббота), представляющее номер дня недели.

На рисунке ниже вы видите, как выглядит готовая таблица. Если ввести год в ячейку C2, Excel выводит все праздники этого года. Конечно же, Новый год, День независимости, День ветеранов и Рождество рассчитать легко.

Все другие праздники можно описать подобным образом: n-й день месяца (за исключением Дня памяти, который немного отличается). Давайте взглянем на День благодарения 2015 года. Если вы поймёте, как вычислить День благодарения, то разберётесь и с остальными праздниками. День благодарения празднуется каждый четвёртый четверг ноября.

=DATE(C2,11,1)+21+CHOOSE(WEEKDAY(DATE(C2,11,1)),4,3,2,1,0,6,5)
=ДАТА(C2;11;1)+21+ВЫБОР(ДЕНЬНЕД(ДАТА(C2;11;1));4;3;2;1;0;6;5)

Календарь ниже поможет понять, когда будет День благодарения в 2015 году:

Пояснение:

• Выражение ДАТА(C2;11;1) — это 1 ноября 2015 года.
• Выражение ДЕНЬНЕД(ДАТА(C2;11;1)) возвращает 1, т.е. воскресенье.
• ВЫБOP(1;4;3;2;1;0;6;5) возвращает 4 (от воскресенья до четверга 4 дня).
• Получаем: 01.11.2015 + 21 + 4 = 26.11.2015

Примечание: Нам нужны 4 дополнительных дня, поскольку до первого четверга в ноябре не хватает 4 дня. К этой дате нужно добавить еще 21 день (3 недели), чтобы получить 4-й четверг ноября.

Не имеет значения, на какой день выпадает 1 ноября. Функция CHOOSE (ВЫБОР) всё равно добавит правильное количество дней до первого четверга ноября. Потом формула прибавит еще 21 день до 4-го четверга ноября. Аналогичным образом эта формула работает для каждого года.

Теперь давайте взглянем на День Мартина Лютера Кинга, который отмечается ежегодно в третий понедельник января:

=DATE(C2,1,1)+14+CHOOSE(WEEKDAY(DATE(C2,1,1)),1,0,6,5,4,3,2)
=ДАТА(C2;1;1)+14+ВЫБОР(ДЕНЬНЕД(ДАТА(C2;1;1));1;0;6;5;4;3;2)

Эта формула почти идентична предыдущей. В этот раз:

• Функция DATE (ДАТА) возвращает 1-е января.
• В аргументах функции CHOOSE (ВЫБОР) позиция со значением 0 находится на втором месте в списке значений, т.к. мы ищем понедельник.
• Если 1 января выпадает на понедельник, получаем: 14 + ВЫБОР(2;1;0;6;5;4;3;2) = 14 + 0 = 14 дней (или 2 недели) до 3-го понедельника января.