Последние несколько недель были для меня очень беспокойными на работе. Будни сумасшедшие, а выходных никогда не бывает достаточно.
В начале этой недели я просматривал наш годовой календарь отпусков, чтобы проверить следующий отпуск и, если возможно, спланировать отпуск.
И это натолкнуло меня на идею создания этого шаблона праздничного календаря в Excel.
В этом уроке я покажу вам, как рассчитать дату определенного праздника (например, Дня труда).
А еще можно получить потрясающий Шаблон праздничного списка Excel (бесплатная загрузка) в конце этого блога.
Нажмите здесь, чтобы скачать шаблон
Формула для расчета праздников в Excel (День труда)
Вот формула для получения даты Дня труда в 2021 году (это первый понедельник сентября).
= DATE (2021,9,1) + IF (WEEKDAY (DATE (2021,9,1), 2)> 1,7-WEEKDAY (DATE (2021,9,1), 2) + 1,1-WEEKDAY ( ДАТА (2021,9,1), 2))
Он возвращает дату как 07 сентября 2021 года, что является днем рабочего дня в 2021 году.
Эту формулу можно легко настроить, чтобы получить дату праздников, приходящихся на определенный будний день месяца (например, День президента или День памяти).
Как работает формула
Поскольку мы знаем, что День труда - 1-й понедельник сентября, мы начинаем с 1 сентября 2021 года.
Следующая формула даст нам порядковый номер 1 сентября 2021 года:
= ДАТА (2021,9,1)
Теперь нам нужно проверить, является ли первый день сентября понедельником. Мы делаем это, используя следующее условие в функции ЕСЛИ:
WEEKDAY (DATE (2021,9,1), 2)> 1
Функция WEEKDAY выдает число в зависимости от того, какой сегодня день недели. Поскольку я использовал 2 в качестве второго аргумента, он вернет 1 для понедельника, 2 для вторника, 3 для среды и так далее.
Если 1-й день сентября не является понедельником, указанное выше условие ЕСЛИ будет ИСТИНА, и оно вернет значение, указанное в следующей части формулы:
ДЕНЬ 7 НЕДЕЛИ (ДАТА (2021,9,1), 2) +1
Вышеупомянутая часть просто вычитает номер дня недели из 7 и добавляет 1, что дает нам значение, которое мы должны добавить к 1 сентября 2021 года, чтобы получить первый понедельник.
А в случае, если 1 сентября 2021 года выпадает на понедельник, следующая формула гарантирует, что результат функции ЕСЛИ равен 0:
1-НЕДЕЛ. (ДАТА (9,1 2021 г.)
Ниже приведена инфографика, которая показывает то же объяснение (создано с использованием 2014 года, а не 2021 года).
Аналогичным образом, если вам нужно рассчитать другие праздники. Например, День президентов, который проводится в третий понедельник февраля, можно рассчитать по следующей формуле:
= ДАТА (B2,2,1) + ЕСЛИ (ДЕНЬ НЕДЕЛИ (ДАТА (B2,2,1); 2)> 1,7-ДЕНЬ НЕДЕЛИ (ДАТА (B2,2,1); 2) + 1,1-ДЕНЬ НЕДЕЛИ ( ДАТА (B2,2,1), 2)) + ((3-1) * 7)
В приведенной выше формуле B2 относится к значению года.
Примечание: Пасха - это праздник, который сложно рассчитать. Я погуглил и нашел формулу, которая вроде бы работает. Однако понятия не имею, как это работает 🙂БОНУС EXCEL ШАБЛОН ПРАЗДНИЧНОГО КАЛЕНДАРЯ
Вот БЕСПЛАТНЫЙ шаблон календаря праздников в Excel, который дает вам:
- Список праздников (в выбранном году).
- Количество дней до следующего отпуска (число в синем поле). Это работает только в текущем году.
- Количество дней до следующих длинных выходных (число в сером поле). Это работает только в текущем году.
Все, что вам нужно сделать, это выбрать год из раскрывающегося списка, и он автоматически покажет вам список праздников в выбранном году.
Я создал это для праздников в США, так как это происходит в определенные дни или дни месяца.
В Индии (где я живу) многие праздники зависят от положения Луны (и многих других факторов), поэтому сложно придумать такой шаблон праздника.
Скачать шаблон календаря праздников в Excel
Функции Excel, использованные для создания этого шаблона праздничного календаря: -> WEEKDAY | ДАТА | ЕСЛИ.
Если вам интересно узнать, как создается этот шаблон календаря праздников, вы можете отобразить столбцы H-J на листе шаблона и увидеть формулы. Вы также можете отобразить Sheet1, где я сохранил данные, используемые для раскрывающегося списка года.
Вам также могут понравиться следующие шаблоны / статьи Excel:
- БЕСПЛАТНЫЙ шаблон календаря на месяц и год в Excel.
- Маршрут отпуска и шаблон упаковочного листа.
- Шаблон отслеживания отпусков / отпусков сотрудников.
- Календарь интегрирован с шаблоном списка дел.
- Как рассчитать количество дней между двумя датами в Excel
