Kalendarz urlopów i nieobecności pracowników w Excelu: saldo, kody i limit osób

Kto jest dziś na urlopie, kto na zwolnieniu, ile dni urlopu zostało Marcie i czy w przyszłym tygodniu w dziale zostaną jeszcze dwie osoby? W wielu firmach odpowiedzi szuka się w pamięci i w mailach. Wystarczy jednak jedna dobrze ułożona tabela w Excelu.

Pokażę, jak zapisać nieobecności w jednym dzienniku, policzyć dni kalendarzowe i robocze, wyliczyć saldo urlopu, sprawdzić nakładanie się okresów i zbudować prosty kalendarz działu z kodami. Na końcu opis gotowego szablonu.

Jak prowadzić ewidencję nieobecności

1. Lista pracowników i parametry

Na jednym arkuszu trzymasz pracowników: imię i nazwisko, stanowisko, dział, wymiar urlopu na rok i urlop przeniesiony z poprzedniego roku. Na drugim parametry: rok, limit osób nieobecnych jednocześnie i lista dni świątecznych. Wymiar urlopu i zasady jego naliczania ustal według lokalnych przepisów i umów.

2. Dziennik nieobecności

Jeden wiersz to jedna nieobecność: pracownik, rodzaj (urlop, zwolnienie lekarskie, dzień wolny, delegacja), data od, data do i podstawa. Rodzaj wybieraj z listy. Urlop dzielony na części wpisujesz jako osobne wiersze.

3. Dni kalendarzowe i robocze

=C2-B2+1 (dni kalendarzowe)

=DNI.ROBOCZE(B2;C2;Parametry!$E$2:$E$20) (dni robocze bez świąt z Twojej listy)

Wybierz, które dni odejmujesz od salda: w zależności od zasad u Ciebie będą to dni robocze albo kalendarzowe bez świąt. Ważne, by było to jednolite.

4. Saldo urlopu

Saldo = wymiar + przeniesienie − wykorzystane − zaplanowane. Wykorzystane to urlopy z datą w przeszłości, zaplanowane z datą w przyszłości:

=SUMA.WARUNKÓW(Nieobecności!$F:$F;Nieobecności!$A:$A;A2;Nieobecności!$B:$B;"Urlop";Nieobecności!$D:$D;"<="&DZIŚ())

Drugą formułę dla zaplanowanych tworzysz z warunkiem „>”&DZIŚ().

5. Kontrola nakładania się

Jeśli jedna osoba ma dwie nieobecności z nakładającymi się datami, to błąd. Formuła w dzienniku:

=JEŻELI(LICZ.WARUNKI(A:A;A2;B:B;"<="&C2;C:C;">="&B2)>1;"Nakładanie";"")

Warunek wykrywa zakresy, które się przecinają (obejmie też siebie samą, stąd >1).

6. Kalendarz działu

Wybierasz miesiąc, a w nagłówkach kolumn pojawiają się kolejne dni. W komórce wstawiasz kod, jeśli dzień mieści się w jakiejś nieobecności osoby. Kody: U urlop, L zwolnienie, W dzień wolny, D delegacja. Na dole liczysz nieobecnych w każdym dniu i barwisz komórkę na czerwono, gdy liczba przekracza limit z parametrów.

Przykład z liczbami

Anna ma wymiar 20 dni, przeniesione 3 dni, wykorzystane 8, zaplanowane 5.

• Saldo: 20 + 3 − 8 − 5 = 10 dni.
• Nowy urlop od poniedziałku 12 do piątku 16 maja: 5 dni kalendarzowych i 5 roboczych.
• Po dopisaniu: saldo 5 dni.

W dziale ośmiu osób limit nieobecnych jednocześnie wynosi 2. Jeśli we wtorek urlop mają Anna, Piotr i Ewa, kalendarz pokaże 3 i pomaluje ten dzień na czerwono, zanim zatwierdzisz trzeci wniosek.

Częste błędy

• Urlopy w mailach i na kartkach. Bez wpisu do dziennika kalendarz nic nie wie.
• Nakładające się okresy. Dwa wpisy tej samej osoby w tych samych dniach zaniżają saldo.
• Święta pominięte w formułach. Wtedy urlop obejmujący święto zabiera zbyt dużo dni.
• Przeniesienie bez zapisu. Saldo początkowe musi być w tabeli, inaczej wynik jest zawsze niższy.
• Brak rozróżnienia rodzajów nieobecności. Zwolnienie lekarskie nie zmniejsza salda urlopu.

Gotowy szablon ewidencji nieobecności

Szablon (płatny) przygotowuje wszystko, co opisane wyżej:

• „Parametry”: rok, wymiar urlopu, dopuszczalna liczba osób nieobecnych jednocześnie, próg dużego salda, dni świąteczne i działy.
• „Pracownicy”: do 60 osób z urlopem przeniesionym, wykorzystanym i zaplanowanym, saldem, zwolnieniami lekarskimi, dniami wolnymi, delegacjami, statusem na dziś i kontrolą.
• „Nieobecności”: do 800 wpisów z dniami kalendarzowymi i roboczymi, statusem i kontrolą nakładania się okresów.
• „Kalendarz”: obecność działu w dowolnym miesiącu z kodami U, L, W, D, liczbą nieobecnych w poszczególne dni i sumami dla pracowników. Przekroczenie limitu świeci na czerwono.
• „Podsumowanie”: nieobecności według miesięcy i rodzajów, kontrola według działów i dwa wykresy.

Z salda odejmowane są dni urlopu bez dni świątecznych z Twojej listy. Wymiar ustalasz sam. W komplecie pusta tabela i przykład z fikcyjnymi danymi. Excel 2007 i nowszy lub LibreOffice, bez makr. Obliczenia są orientacyjne, a progi i wartości sprawdź z obowiązującymi przepisami.

FAQ

Czy zwolnienie lekarskie zmniejsza saldo urlopu?

Nie, to osobny rodzaj nieobecności. Zapisujesz go w dzienniku, ale nie odejmujesz od puli urlopu. Zasady wpływu choroby na urlop sprawdź w lokalnych przepisach.

Jak wpisać urlop w trakcie którego wypada weekend?

Wpisz daty od i do. Jeśli saldo ma liczyć tylko dni robocze, formuła DNI.ROBOCZE pominie soboty i niedziele oraz święta z listy.

Czy mogę ustawić limit osób nieobecnych osobno dla działów?

W prostym modelu jest jeden limit. Dla różnych działów wystarczy osobny kalendarz lub osobna kolumna limitów i warunek z działem w formule zliczającej.

Jak zacząć nowy rok?

Zmień rok w parametrach, wpisz przeniesione dni i dopisz nowe nieobecności. Dziennik z poprzedniego roku zachowaj w archiwum.