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