Календар робочого часу (виробничий календар) містить інформацію щодо кількості робочих, вихідних, святкових днів.Кількість визначається помісячно за вказаний рік у комірці А2, та в цілому за квартал і рік. Також у календарі зазначаються норми тривалості робочого часу.
Основними складовими українського виробничого календаря є:
Робочий тиждень: Зазвичай в Україні робочий тиждень триває з понеділка по п'ятницю. Субота і неділя є вихідними днями.
Святкові дні: Виробничий календар України також включає святкові дні, такі як Новий Рік (1 січня), Міжнародний жіночий день (8 березня), День пам'яті та перемоги над нацизмом у Другій світовій війні 1939 – 1945 років (8 травня) та інші, які визначені законодавством України.
Місцеві особливості чи переноси погоджені урядом: Деякі регіони України можуть мати власні святкові дні або традиції, які також можуть бути враховані у виробничому календарі.
Загалом, виробничий календар України регулюється законодавством та враховується при плануванні робочого часу та відпусток працівників.
Скласти календар робочого часу у таблицях не просто, але можна, тому алгоритм і формули нижче, в нас буде ще й, візуалізація свят і переносів на календарі який автоматично будується від вказаного року. Всі кольорові позначки - вказано нижче. Відео тут YouTube.
Крок 1. Таблиця тривалість робочого часу, враховуємо воєнний стан (початок дії і майбутній кінець).
За загальним правилом нормальна тривалість робочого часу працівників не може перевищувати 40 годин на тиждень (ст. 50 КЗпП). Але у період дії військового стану для працівників, зайнятих на об’єктах критичної інфраструктури (в оборонній сфері, сфері забезпечення життєдіяльності населення тощо), її можна збільшити до 60 годин на тиждень (ч. 1 ст. 6 Закону № 2136).
Також ст. 51 КЗпП для окремих категорій працівників встановлює скорочену тривалість робочого часу. Проте, знов-таки, якщо такі працівники зайняті на об’єктах критичної інфраструктури (в оборонній сфері, сфері забезпечення життєдіяльності населення тощо), то для них скорочену тривалість робочого часу дозволено збільшити до 40 годин на тиждень (ч. 2 ст. 6 Закону № 2136).
Крок 2. Визначаємося з тривалістю робочого тижня (п’ятиденний чи шестиденний), вихідними днями, тривалістю щоденної роботи працівників.
Крок 3. Для кожного місяця календарного року(клітинка А2 - робимо спадний список із роками) і всього за рік визначаємо на Листі "По днях" - для кожної дати вказаного року - чи є вона:
Державне свято - слідкуємо за нововведеннями.
Вихідний (субота, неділя)
Перенос свята з вихідного дня (збіг державного свята із суботою, неділею)
Затверджені урядом канікули (якщо між святами додають вихідний, який відпрацьовуємо у суботу) - вносимо руками після затвердження.
Відпрацювання канікул по суботах
Робочий день
Скорочений день
Воєнний стан
Конкретні дати визначаємо на листі Довідник_handmade діапазон H2:R20, і якщо у стовпчику А (дата) на Листі "По днях" знаходимо цю дату виставляємо TRUE у відповідну колонку.
Ці дані наддадуть нам можливість порахувати все точно і з усіма умовами за стовпчиком К (календар) в якому записуємо чим є кожна дата року.
=IF(AND(E2=TRUE; B2<6); "р";
IF( AND(E2=TRUE; B2>5); "в";
IF(F2=TRUE; "с";
IF(G2=TRUE; "п";
IF(H2=TRUE; "х";
IF(I2=TRUE; "ж";
IF(J2=TRUE; "о";
IF( B2>5; "в"; "р"
))))))))
*Це все нам треба, ще і для умовного форматування календаря.
Формула проходить по всіх умовах, щоб визначити тип дня на основі значень в комірках. Вона перевіряє послідовність умов і повертає відповідний код:
"р" — робочий день
"в" — вихідний (субота, неділя)
"с" — державне свято
"п" — скорочений день
"х" — перенесення свята на вихідний день
"ж" — затверджені урядом канікули
"о" — відпрацювання канікул по суботах
Кожна умова перевіряється послідовно, і як тільки знайдено відповідну умову, формула зупиняється на ній і повертає значення.
Формула працює з кількома умовами, які перевіряють значення в комірках B2, E2, F2, G2, H2, I2, і J2, щоб визначити тип дня. Кожна умова повертає певний символ, що представляє тип дня.
IF(AND(E2=TRUE; B2<6); "р";
Якщо в комірці E2 стоїть TRUE і значення в B2 менше за 6, то це означає, що це робочий день. Формула повертає "р".
IF( AND(E2=TRUE; B2>5); "в";
Якщо в E2 стоїть TRUE, але значення B2 більше за 5 (наприклад, субота або неділя), то це вихідний день. Формула повертає "в".
IF(F2=TRUE; "с";
Якщо в F2 стоїть TRUE, це означає, що це державне свято. Формула повертає "с".
IF(G2=TRUE; "п";
Якщо в G2 стоїть TRUE, це означає, що це скорочений день. Формула повертає "п".
IF(H2=TRUE; "х";
Якщо в H2 стоїть TRUE, це означає перенесене свято (збіг державного свята з вихідним). Формула повертає "х".
IF(I2=TRUE; "ж";
Якщо в I2 стоїть TRUE, це означає затверджені урядом канікули (вихідний між святами, який потрібно буде відпрацьовувати). Формула повертає "ж".
IF(J2=TRUE; "о";
Якщо в J2 стоїть TRUE, це означає відпрацювання канікул по суботах. Формула повертає "о".
IF( B2>5; "в"; "р"))))))))
Якщо значення в B2 більше за 5 (наприклад, субота або неділя), це вихідний день, тому формула повертає "в".
Якщо ж значення B2 не більше за 5, то це робочий день. Формула повертає "р".
1) Кількість календарних днів, рядок 3 рахуємо формулою:
=DAY(EOMONTH(DATE($A$2;B$2;1);0))
Що робить формула:
DATE($A$2;B$2;1)
Ця частина створює дату, використовуючи значення року з комірки A2, значення місяця з рядка, що знаходиться у (B2), і день 1 - початок кожного місяця.
EOMONTH(DATE($A$2;B$2;1);0))
Функція EOMONTH повертає останній день місяця для вказаної дати. У цьому випадку ми використовуємо 0 як другий аргумент, що означає, що ми хочемо отримати останній день поточного місяця.
DAY(EOMONTH(DATE($A$2;B$2;1);0))
Цей фрагмент витягує день місяця з дати, яку повернула функція EOMONTH.
Отже, в результаті отримуємо день місяця останнього дня вказаного місяця і року, що відповідає кількості календарних днів.
2) Кількість святкових і неробочих днів (робимо окремий Довідник_handmade із переліком свят з датою початку і датою скасування)окремо отримуємо Великдень на листку Свята_web.
Святкові та неробочі дні визначені ст. 73 КЗпП. Наразі у 2024 їх одинадцять: 8 святкових днів та 3 неробочих дні.
01.01 Новий Рік
08.03 Міжнародний жіночий день
рухоме Великдень за юліанським календарем
01.05 День праці
08.05 День пам'яті та перемоги над нацизмом у Другій світовій війні 1939 – 1945 років
рухоме: Великдень + 49 днів Трійця за юліанським календарем
28.06 День Конституції України
15.07 День Української Державності
24.08 День Незалежності України
01.10 День захисників і захисниць України
25.12 Різдво Христове
07.01 Різдво Христове (за юліанським календарем)
=COUNTIFS(
'По днях_handmade'!$K$2:$K$400; "с";
'По днях_handmade'!$C$2:$C$400; B$2;
'По днях_handmade'!$D$2:$D$400; $A$2
)
Формула рахує кількість рядків, що відповідають усім трьом умовам:
Значення у колонці K дорівнює "с" (державне свято).
Значення у колонці C дорівнює значенню у комірці B2 - місяць.
Значення у колонці D дорівнює значенню у комірці A2 - рік.
Тобто, формула визначає кількість рядків, які позначені як "державне свято" у колонці K, і де значення у колонках C і D співпадають із значеннями у відповідних комірках B2 та A2.
Довідники потрібні для визначення дати свята і чи у вказаному році $A$2 лист Виробничий календар його святкують, щоб правильно порахувати кількість святкових днів, переносів при співпадінні вихідних і свят, а також для визначення чи є свята підряд.
Нажаль офіційного джерела із переліком Державних свят і їх змінами, мені не вдалось знайти, тому статична інформація взята з Вікіпедії і з веб ресурса https://date.nager.at/Api, бо дату Великодня це жесть отримуємо формулою:
=TRANSPOSE(
IMPORTDATA(
"https://date.nager.at/api/v3/publicholidays/" &
TEXT('Виробничий календар'!$A$2;"##") &
"/UA";
"{"
)
)
Як працювати із даними API і розбирати їх на елементи, розглядали в попередніх матеріалах тут: https://youtu.be/ESOSf6J6BCM
Під час дії воєнного стану ст. 73 КЗпП не працює, тому святкові та неробочі дні є звичайними днями календаря для цього у рядку 1 є дата Початок військового стану С1 і кінець E1. Вказавши в них дати - в цей період святкові дні і скорочені не враховуватимуться.
3) кількість вихідних днів - =IF(A2="";""; WEEKDAY(A2;2)) , номер дня тижня 6 і 7 - відповідає суботі і неділі.
Якщо після завершення дії воєнного стану маємо святковий/неробочий день, який збігається з вихідним, вихідний день переноситься на наступний робочий день (ч. 3 ст. 67 КЗпП) - все це враховано у Довіднику і на окремому листочку по .
Врахуйте, що внаслідок збільшення тижневої тривалості робочого часу протягом воєнного стану тривалість щотижневого безперервного відпочинку може бути скорочена до 24 годин (ч. 5 ст. 6 Закону № 2136). Винятком є неповнолітні, для яких безперервний щотижневий відпочинок не може становити менше ніж 42 години (ч. 8 ст. 6 Закону № 2136, ст. 70 КЗпП).
4) кількість робочих днів (порахуємо р+п+о);
=COUNTIFS(
'По днях_handmade'!$K$2:$K$400; "р";
'По днях_handmade'!$C$2:$C$400; B$2;
'По днях_handmade'!$D$2:$D$400; $A$2
)
+ COUNTIFS(
'По днях_handmade'!$K$2:$K$400; "п";
'По днях_handmade'!$C$2:$C$400; B$2;
'По днях_handmade'!$D$2:$D$400; $A$2
)
+ COUNTIFS(
'По днях_handmade'!$K$2:$K$400; "о";
'По днях_handmade'!$C$2:$C$400; B$2;
'По днях_handmade'!$D$2:$D$400; $A$2
)
Використовуємо функцію COUNTIFS, яка дозволяє рахувати кількість елементів у діапазоні, що відповідають декільком умовам одночасно.
Формула підсумовує результати всіх трьох частин, щоб отримати загальну кількість рядків, які відповідають таким умовам:
Значення в колонці K дорівнює "р", "п" або "о" (робочий день, скорочений день або відпрацювання).
Значення в колонці C дорівнює значенню у комірці B2.
Значення в колонці D дорівнює значенню у комірці A2.
Таким чином, ця формула рахує кількість певних типів днів (робочі, скорочені, відпрацювання) у фільтрі, залежно від значень у B2 та A2 на листі По днях_handmade.
5) кількість робочих днів (у період після завершення воєнного стану), які передують святковим і неробочим дням (число місяця, в яке скорочується тривалість робочого дня). У такі дні тривалість робочого дня (зміни) працівників з нормальною тривалістю робочого часу зменшується на 1 годину (ч. 1 ст. 53 КЗпП). Але знову таки, під час дії воєнного стану таке скорочення робочого дня не відбувається.
Крок 4. Підраховуємо норму робочого часу для різних категорій працівників, залежно від тривалості робочого часу на робочий тиждень
Календар на рік - формули і умовне форматування.
=SEQUENCE(
6;
7;
IF(
WEEKDAY(DATE($A$2; B2; 1); 2) = 1;
DATE($A$2; B2; 1);
DATE($A$2; B2; 1) - WEEKDAY(DATE($A$2; B2; 1); 3)
);
1
)
Ця формула створює послідовність із 6 дат, починаючи з дати в комірці B2 і виключаючи вихідні, яка представляє календар на місяць, починаючи з першого понеділка певного місяця. Тобто генерує таблицю розміром 6×7 (6 тижнів по 7 днів), починаючи з першого понеділка вказаного місяця.
Щоб отримати календар на 12 місяців необхідно цю формулу розмножити на 12 матриць, кожна з яких буде починатися з попереднього дня тижня.
Розглянемо формулу крок за кроком:
SEQUENCE(6;7; ... )
Функція SEQUENCE створює таблицю чисел (у нашому випадку — дат).
Формат SEQUENCE(rows; columns; start; step):
rows – кількість рядків (у нашому випадку 6).
columns – кількість стовпців (у нашому випадку 7, тобто 7 днів тижня).
start – початкове значення для послідовності (це буде перша дата місяця або найближчий понеділок).
step – крок між значеннями (у нашому випадку 1 день).
Тобто SEQUENCE(6; 7; ...) створює таблицю з 6 рядків і 7 стовпців, яка представляє календар на місяць.
DATE($A$2;B2;1)
DATE(year; month; day) створює дату на основі року, місяця та дня.
Рік береться з комірки $A$2.
Місяць береться з комірки B2.
День — це 1, тобто перший день місяця.
Таким чином, DATE($A$2; B2; 1) створює перше число заданого місяця в році.
WEEKDAY(date($A$2;B2;1);2)
WEEKDAY(date; type) повертає номер дня тижня для певної дати.
type = 2 означає, що понеділок буде вважатися днем 1, вівторок — днем 2 і т.д. (від 1 до 7, де понеділок — 1, неділя — 7).
Отже, WEEKDAY(DATE($A$2; B2; 1); 2) повертає номер дня тижня для першого числа місяця.
IF(WEEKDAY(DATE($A$2;B2;1);2)=1;DATE($A$2;B2;1);date($A$2;B2;1)-WEEKDAY(date($A$2;B2;1);3))
Ця частина формули визначає, з якої дати почнеться календар.
IF перевіряє, чи є перший день місяця (тобто DATE($A$2; B2; 1)) понеділком (тобто, чи WEEKDAY(...; 2) = 1).
Якщо перший день місяця є понеділком (WEEKDAY(...; 2)= 1), то початкова дата календаря— це перше число місяця (DATE($A$2;B2; 1).
Якщо перший день місяця НЕ є понеділком, то календар має починатися з попереднього понеділка. Щоб знайти цей понеділок, ми віднімаємо WEEKDAY(...; 3) від DATE($A$2; B2; 1).
WEEKDAY(...; 3) повертає значення від 0 (понеділок) до 6 (неділя), яке показує, скільки днів потрібно відняти від дати, щоб отримати попередній понеділок.
SEQUENCE(6 ; 7; start_date; 1)
Всі розрахунки у функції IF() визначають початкову дату для послідовності (start_date).
Крок 1 вказує на те, що ми збільшуємо дату на один день для кожної наступної комірки.
Таким чином, формула створює 6 рядків і 7 стовпців (представляючи календар на 6 тижнів), де:
Початкова дата обчислюється як перший понеділок місяця або попередній понеділок відносно першого числа місяця.
Ось деякі додаткові моменти, які слід врахувати:
Ця формула припускає, що ваш тиждень починається з понеділка і закінчується в неділю. Якщо ваш тиждень починається в інший день, вам потрібно буде відповідно відкоригувати формулу.
Ви можете змінити розмір матриці дат, змінивши перший аргумент у функції SEQUENCE.
Цю формулу можна скопіювати в інші комірки, щоб генерувати серію послідовностей будніх днів, починаючи з різних дат.
Щоб вийшов справжній календар - додамо кольорів, але для цього треба зрозуміти Ху із Ху - ,окремо робимо отаку табличку BJ4:CN23 , куди XLOOKUPом вносимо ознаку дати.
=XLOOKUP(V4;'По днях_handmade'!$A$2:$A$367; 'По днях_handmade'!$K$2:$K$367;"";0)
Якщо V4 містить значення 01.01.2015 , і у стовпці 'По днях_handmade'!$A$2:$A$367 є значення 01.01.2015 у рядку 2, то формула поверне значення з 'По днях_handmade'!$K$10 - "с" - свято.
Якщо ж V4 не знаходиться в діапазоні 'По днях_handmade'!$A$2:$A$367, формула поверне порожній рядок.
Тепер налаштовуємо всі умови шо і як фарбувати.
"р" — робочий день, "в" — вихідний (субота, неділя),"с" — державне свято, "п" — скорочений день, "х" — перенесення свята на вихідний день, "ж" — затверджені урядом канікули, "о" — відпрацювання канікул по суботах.
Таблиця з позначками кожної окремої дати для застосування умовного форматування.
По суті накладаємо діапазон із буквами на таблицю із датами.
Тобто пофарбуй 01.01.2015 в свято, якщо на його місці с. =BJ4="с" для діапазону V3:AB23
Що ж, вітаю, ви перейшли на новий левел. Нижче готовий Виробничий календар, ну й просто календар.
Дивіться мої відоси, пишіть коментарі, люблю з вами спілкуватися і люблю коли мене хвалять, тому вподобайки будь ласка😀.
