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
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Ą
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
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
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
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
No to” mam i ja” kolejny pomocny plik dla kadrowej mieszającej w stażach pracy pracowników Dziękuje
🙂 BAAARDZO się cieszę 🙂
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)
Super pomysł! Dziękuję za podzielenie się 🙂
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
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
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
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”
Niesamowicie długa formuła :). Jeśli działa, to najważniejsze!
…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ć…
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
Hmmm… a może po prostu jego okres „pracujący” zapisać w tym arkuszu jako oddzielne zatrudnienia? Wystarczy tak?
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)
Super, że się udało :). Cytując „Kilera”: obie wpadłyśmy na ten sam plan 😉
Nie wolno liczyć miesiąca jako 30 dni!
https://ksiegowosc.infor.pl/zus-kadry/inne/53977,Jak-obliczyc-staz-pracy.html
Hej, dziękuję. To było potrzebne uczestniczce do wyliczania nagród i takie mieli tam zasady liczenia 🙂