fbpx

Staż pracy na podstawie świadectw pracy

02.11.2017 | Czas pracy, ECP2, HR

Czyli jak obliczyć ile pracownik pracował łącznie lat, miesięcy i dni

Czyli znów o funkcjach zaokrąglających!

Niby proste zadanie: obliczyć łączny staż pracy pracownika, mając dane jego dotychczasowe świadectwa pracy. Skoro na takim świadectwie jest rozróżnione ile lat, miesięcy i dni pracował – to niby wystarczy prosta suma.

Otóż nie.

Z takiej sumy może nam wyjść, że pracownik pracował 65 dni, co daje nam już 2 dodatkowe miesiące i 5 dni (zgodnie z wytycznymi – miesiąc traktujemy jako 30 dni). I te 2 miesiące należy dodać do sumy miesięcy. Gorzej, jak wyjdzie nam ona 12 lub większa – wtedy mamy kolejny rok…

W tym wpisie pokażę wam, jak sobie z tym poradzić (uwaga!  działa dla Excela 2013 i wyższych).

 

Chodzi o coś takiego:

Formatka

Formatka

Wiemy już jak wygląda formatka, zatem do dzieła!

Mamy wpisane jakieś przykładowe dane (oczywiście podchwytliwe ;)), więc pierwszy krok, to prosta suma (komórka H4 i w dół). To będzie podstawa dalszej pracy i rozmyślań:

=SUMA(C4:G4)

Dostajemy w wyniku coś takiego:

Podsumowanie prostą SUMĄ

Podsumowanie prostą SUMĄ

I oczywiście widać, że to nie wystarczy. Choćby te 65 dni powinno być zamienione na dodatkowe 2 miesiące, a dni powinno być 5. Tak samo z miesiącami i latami.

Zajmę się poszczególnymi wynikami w kolejności: dzień, miesiąc, rok, czyli od końca tabelki.

Podsumowanie dni

Czyli tutaj obowiązuje zasada, że jeśli liczba dni jest większa lub równa 30, to interesuje nas tylko liczba dni, która zostaje po odjęciu pełnych miesięcy. Jako pełny miesiąc rozumiemy 30 dni.

Prosto powiedzieć, ale pytanie jak to zrobić w Excelu?

Otóż weźmy sobie tę naszą liczbę 65. Można ją zapisać jako 65 = 30 + 30 + 5. Excel sam z siebie tergo tak nie rozpisze, ale będzie umiał obliczyć ile pełnych 30 mieści się w 65. Doskonale nadaje się do tego funkcja ZAOKR.W.DÓŁ.MATEMATYCZNE (uwaga! działa dla Excela 2013 i wyższych). Wyświetla ona liczbę zaokrągloną do podanej istotności, ale mniejszą od tej liczby.

Wiem, czarna magia :). Chodzi o to, że funkcja ta wyświetla taką liczbę, która jest podzielna przez ustalony przez nas dzielnik (istotność) i mieści się w zadanej przez nas wartości. Czyli w naszym przypadku, jeśli mamy 65 dni, to w liczbie dni chcemy wyświetlić wynik takiego działania: 65 – 60 = 5. I to 60 zwróci nam właśnie funkcja ZAOKR.W.DÓŁ.MATEMATYCZNE. Każemy jej wyświetlić liczbę mieszczącą się w 65, ale podzielną przez 30. Taką liczbą jest właśnie 60.

A ponieważ chcemy ją dojąć od ogólnej liczby dni i to tylko wtedy, gdy , to formuła w komórce I6 będzie taka:

=H6-ZAOKR.W.DÓŁ.MATEMATYCZNE(H6;30)

Dla wersji Excela 2010 i niższych, można zastosować następujący zamiennik:

=H6-ZAOKR.DO.CAŁK(H6/30)*30

Funkcji tej nie muszę wrzucać w JEŻELI, ponieważ wyzeruje się ona, jeśli liczba dni będzie mniejsza od 30. Żadna bowiem taka liczba nie będzie podzielna przez 30. That’s it. Oto wynik:

Liczba nadwyżkowych dni

Liczba „nadwyżkowych: dni

Liczbę dni mamy. Jedziemy do miesięcy.

Podsumowanie miesięcy

Miesiące mają identyczną logikę, tylko jeszcze dodatkowo uwzględniają nadwyżkę dni z wiersza poniżej. I oczywiście to stanowi tutaj trudność 🙂

Czyli znowu: od sumarycznej liczby miesięcy (łącznie z nadwyżkowymi dniami!) odejmujemy pełne lata (12 miesięcy), które pochodzą też z tej sumarycznej liczby miesięcy (uff, dobrze, że jeszcze tylko rok został, bo się zapętlamy).

Tę sumaryczną liczbę miesięcy można obliczyć korzystając z poprzednich wyników: do liczby miesięcy (H5) dodać różnicę dni minus nadwyżkowe dni, podzielone przez 30 (żeby uzyskać liczbę miesięcy). Potem od tego należy odjąć liczbę lat, która z tego wychodzi, czyli tutaj znowu funkcja ZAOKR.W.DÓŁ.MATEMATYCZNE, tylko tym razem istotnością będzie 12.

Wszystko razem daje nam taką formułę w komórce I5:

=H5+(H6-I6)/30-ZAOKR.W.DÓŁ.MATEMATYCZNE(H5+(H6-I6)/30;12)

Pierwsza część tej formuły ustala ogólną liczbę miesięcy, a druga ustala ile z tych dni powinno być zgrupowane w lata. Efekt jest taki:

Liczba nadwyżkowych miesięcy

Liczba nadwyżkowych miesięcy

Dla Excela 2010 i niższych zadziała to:

=H5+ZAOKR.DO.CAŁK(H6/30)-ZAOKR.DO.CAŁK((H5+ZAOKR.DO.CAŁK(H6/30))/12)*12

Podsumowanie lat

I ostatnie: łączna liczba przepracowanych lat.

Tutaj do sumy lat (H4) dodajemy lata, które nazbierały się z miesięcy i dni. Wiemy z poprzedniej formuły, że łączne miesiące to H5+(H6-I6)/30. Jeśli więc to podzielimy przez 12 i obetniemy część dziesiętną od wyniku – dostaniemy liczbę pełnych, nadwyżkowych lat. Funkcja ZAOKR.DO.CAŁK pięknie obcina miejsca dziesiętne, ponieważ zaokrągla liczbę w dół.

A więc formuła jest taka (I4):

=H4+ZAOKR.DO.CAŁK((H5+(H6-I6)/30)/12)

I w wyniku otrzymujemy już komplet danych:

Wynik

Wynik

I dla niższych wersji Excela taka formuła:

=H4+ZAOKR.DO.CAŁK((H5+ZAOKR.DO.CAŁK(H6/30))/12)

I to tyle. Dwie funkcje zaokrąglające i mamy wszystko, czego potrzebowaliśmy do szczęścia! No może jeszcze wersja wideo i plik z gotowcem do pobrania 🙂

 Poniżej pliki do pobrania:

MalinowyExcel Staż pracy na podstawie świadectw pracy dla Excela 2010 i niższych.xlsx

MalinowyExcel Staż pracy na podstawie świadectw pracy dla Excela 2013 i wyższych.xlsx

 

 

Jeżeli chcesz lepiej poznać Excela na poziomie średniozaawansowanym zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 2! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

17 komentarzy

  1. No to” mam i ja” kolejny pomocny plik dla kadrowej mieszającej w stażach pracy pracowników Dziękuje

    Reply
    • 🙂 BAAARDZO się cieszę 🙂

      Reply
  2. A ja bym użyła kolejno dla:
    lat: H4+CZ.CAŁK.DZIELENIA(H5;12)
    miesięcy: MOD(H5;12)+CZ.CAŁK.DZIELENIA(H6;30)
    dni: MOD(H6;30)

    Reply
    • Super pomysł! Dziękuję za podzielenie się 🙂

      Reply
  3. Mogę prosić o pomoc w rozbudowie arkusza o wyliczanie stażu pracy (lata, miesiące, dni) na podstawie wpisanych dat rozpoczęcia i zakończenia kolejnych okresów zatrudnienia (np. Kowalski Jan ma 5 różnych (nie kolejnych) okresów zatrudnienia)…

    z góry dziękuję za pomoc

    Reply
  4. Mogę prosić o pomoc w rozbudowie tego arkusza (staż pracy) o automatyczne wyliczenia na podstawie kilku (nie kolejnych) dat rozpoczęcia i zakończenia pracy ogólnego/sumarycznego stażu pracy w formacie x lat(a), x miesiąc(e),x dzień/dni.
    Np. mam daty dotyczące 5 okresów (różnych) zatrudnienia Jana Kowalskiego i chciałbym po wpisaniu tych dat otrzymać sumę czyli łączny staż pracy.

    i jeszcze jeden problem jak zamienić/przeliczyć ilość dni (np. 6413) na format: x lat(a), x miesiąc(e),x dzień/dni.

    z góry dziękuję za pomoc

    Reply
    • Hej,
      jeśli chodzi o wyliczanie stażu pracy na kilka kolejnych okresów – nie obiecuję, ale będę miała w pamięci.
      Odnośnie przeliczenia dni: wszystko zależy od zasad, jakimi się kierujemy. Na podstawie samej tylko ilości dni, można przyjąć zasadę, że rok ma 365 dni (czyli po prostu podzielić jedno przez drugie). Pozostałe dni (resztę z dzielenia) można podzielić na 30 i to będzie liczba miesięcy. To jest jednak propozycja – wiemy przecież, że nie każdy rok ma 365 dni, a miesiąc 30. Stąd pisałam o zasadzie. Łatwiej byłoby to zrobić, gdybyśmy mieli konkretne daty :).
      Pozdrawiam
      Malina

      Reply
      • dla dziesięciu okresów zatrudnienia (10 par dat – E4-F4,….W4-X4) „na szybko” powstała taka big formuła:

        =(DATA.RÓŻNICA($E4;$F4;”Y”))+(DATA.RÓŻNICA($G4;$H4;”Y”))+(DATA.RÓŻNICA($I4;$J4;”Y”))+(DATA.RÓŻNICA($K4;$L4;”Y”))+(DATA.RÓŻNICA($M4;$N4;”Y”))+(DATA.RÓŻNICA($O4;$P4;”Y”))+(DATA.RÓŻNICA($Q4;$R4;”Y”))+(DATA.RÓŻNICA($S4;$T4;”Y”))+(DATA.RÓŻNICA($U4;$V4;”Y”))+(DATA.RÓŻNICA($W4;$X4;”Y”))+ZAOKR.DÓŁ(((DATA.RÓŻNICA($E4;$F4;”YM”))+(DATA.RÓŻNICA($G4;$H4;”YM”))+(DATA.RÓŻNICA($I4;$J4;”YM”))+(DATA.RÓŻNICA($K4;$L4;”YM”))+(DATA.RÓŻNICA($M4;$N4;”YM”))+(DATA.RÓŻNICA($O4;$P4;”YM”))+(DATA.RÓŻNICA($Q4;$R4;”YM”))+(DATA.RÓŻNICA($S4;$T4;”YM”))+(DATA.RÓŻNICA($U4;$V4;”YM”))+(DATA.RÓŻNICA($W4;$X4;”YM”))+ZAOKR.DÓŁ(((DATA.RÓŻNICA($E4;$F4;”MD”))+(DATA.RÓŻNICA($G4;$H4;”MD”))+(DATA.RÓŻNICA($I4;$J4;”MD”))+(DATA.RÓŻNICA($K4;$L4;”MD”))+(DATA.RÓŻNICA($M4;$N4;”MD”))+(DATA.RÓŻNICA($O4;$P4;”MD”))+(DATA.RÓŻNICA($Q4;$R4;”MD”))+(DATA.RÓŻNICA($S4;$T4;”MD”))+(DATA.RÓŻNICA($U4;$V4;”MD”))+(DATA.RÓŻNICA($W4;$X4;”MD”)))/30;0))/12;0)&” lat(a), „&MOD((DATA.RÓŻNICA($E4;$F4;”YM”))+(DATA.RÓŻNICA($G4;$H4;”YM”))+(DATA.RÓŻNICA($I4;$J4;”YM”))+(DATA.RÓŻNICA($K4;$L4;”YM”))+(DATA.RÓŻNICA($M4;$N4;”YM”))+(DATA.RÓŻNICA($O4;$P4;”YM”))+(DATA.RÓŻNICA($Q4;$R4;”YM”))+(DATA.RÓŻNICA($S4;$T4;”YM”))+(DATA.RÓŻNICA($U4;$V4;”YM”))+(DATA.RÓŻNICA($W4;$X4;”YM”))+ZAOKR.DÓŁ(((DATA.RÓŻNICA($E4;$F4;”MD”))+(DATA.RÓŻNICA($G4;$H4;”MD”))+(DATA.RÓŻNICA($I4;$J4;”MD”))+(DATA.RÓŻNICA($K4;$L4;”MD”))+(DATA.RÓŻNICA($M4;$N4;”MD”))+(DATA.RÓŻNICA($O4;$P4;”MD”))+(DATA.RÓŻNICA($Q4;$R4;”MD”))+(DATA.RÓŻNICA($S4;$T4;”MD”))+(DATA.RÓŻNICA($U4;$V4;”MD”))+(DATA.RÓŻNICA($W4;$X4;”MD”)))/30;0);12)&” miesiąc(y), „&MOD((DATA.RÓŻNICA($E4;$F4;”MD”))+(DATA.RÓŻNICA($G4;$H4;”MD”))+(DATA.RÓŻNICA($I4;$J4;”MD”))+(DATA.RÓŻNICA($K4;$L4;”MD”))+(DATA.RÓŻNICA($M4;$N4;”MD”))+(DATA.RÓŻNICA($O4;$P4;”MD”))+(DATA.RÓŻNICA($Q4;$R4;”MD”))+(DATA.RÓŻNICA($S4;$T4;”MD”))+(DATA.RÓŻNICA($U4;$V4;”MD”))+(DATA.RÓŻNICA($W4;$X4;”MD”));30)&” dni”

        Reply
        • Niesamowicie długa formuła :). Jeśli działa, to najważniejsze!

          Reply
          • …działa…chociaż ogólnie jest problem z obliczaniem stażu pracy; sprawdziłem kilka internetowych/on-line kalkulatorów i dla 3 par dat dostałem aż 3 różne grupy wyników (różnią się od mojej o 2, 3, 10 dni)…hm nie wiem czy mojej nie zmodyfikować o dodanie 1 dnia, bo w przypadku pracy od 2000-02-01 do 2000-02-01 dostajemy wynik 0…a przecież przepracowaliśmy 1 dzień 😉

          • Rozumiem… no właśnie – wszystko zależy od zdefiniowania sposobu obliczania… Tak, jak najbardziej dodaj 1 dzień:daty to liczby i stąd ten problem. Gdyby dodać jeszcze godziny pracy, to wtedy wyszłoby matematycznie ok, ale znów chodzi o dni itd… pełno tu skrótów myślowych, przed którymi trzeba Excela zabezpieczać…

  5. A czy mogłabym prosić o pomoc w rozbudowaniu arkusza o odjęcie urlopu bezpłatnego?
    Mam pracownika, który 27 razy w ciągu kilku lat pracy korzystał z urlopu, po podsumowaniu mam problem z odjęciem tak, żeby wszystko grało co do dnia.
    Z góry dziękuję za pomoc

    Reply
    • Hmmm… a może po prostu jego okres „pracujący” zapisać w tym arkuszu jako oddzielne zatrudnienia? Wystarczy tak?

      Reply
      • Koniec końców tak zrobiłam, cały okres zatrudnienia rozbiłam na „cząstki” kiedy był w szkole i w sumie wyszło prawie 30 okresów zatrudnienia :o)

        Reply
        • Super, że się udało :). Cytując „Kilera”: obie wpadłyśmy na ten sam plan 😉

          Reply
    • Hej, dziękuję. To było potrzebne uczestniczce do wyliczania nagród i takie mieli tam zasady liczenia 🙂

      Reply

Submit a Comment

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest