Dziś chciałam napisać o czymś innym, ale Łukasz skutecznie skłonił mnie, abym znowu zajęła się tematem godzin nocnych;) Łukasz zauważył, że w poprzednim wpisie, a co za tym idzie – w poprzedniej formule – jest błąd. Nieprawidłowo liczyła ona bowiem czas pracy nocny, gdy pracownik zaczynał pracę o 6:00 (godzina graniczna godzin nocnych), a kończył o 23:00 (już w trakcie godzin nocnych). Czyli przepracował 1 godzinę nocną. Łukasz – dzięki wielkie za uwagę i wytrwałość w przypominaniu mi o temacie 😉
Poprzednia formuła wyglądała tak:
=JEŻELI(ORAZ(A6>=nocna_do;A6<nocna_od;B6>nocna_do;B6<=nocna_od;D6<=nocna_od);0;JEŻELI(ORAZ(C6>=nocna_od;D6<=nocna_do+1);D6-C6;JEŻELI(ORAZ(C6<nocna_od;D6<=nocna_do+1;C6>nocna_do);D6-nocna_od;JEŻELI(ORAZ(LUB(C6>=nocna_od;C6<nocna_do);D6>nocna_do+1);nocna_do+1-C6;JEŻELI(ORAZ(C6<nocna_od;D6>nocna_do+1);nocna_do+1-nocna_od;0)))))
Przyznam, że perspektywa analizy formuły-tasiemca wcale mi się nie widziała… Zabierałam się więc do tego jak pies do jeża. Nie mogłam jednak odkładać tego w nieskończoność, więc w końcu zasiadłam do pracy. Oczywiście, jak zobaczyłam formułę (dla przypomnienia wyklejam ją powyżej), to się przeraziłam! Analiza jej zajęłaby mi wieki i z pewnością poszarpałabym sobie na niej nerwy, a tego chciałam za wszelką cenę uniknąć 😉 Stwierdziłam więc, że napiszę ją od nowa. Oczywiście oznaczało to dla mnie wgryzanie się w temat na nowo i wymyślanie wszystkich możliwych opcji na nowo… Ech, no cóż. Do dzieła!
Szukasz szkolenia z Excela dla HR?
Najbliższy termin: 25-26 lutego 2020
(szkolenie stacjonarne, Warszawa)
Zobacz szczegóły
Założenia mojego rozumowania i wyliczeń były takie:
- Pracownik pracuje krócej niż 24 godziny, czyli:
- Godzina wejścia i godzina wyjścia nie mogą się sobie równać.
- Wpisywane są tylko godziny wejścia i wyjścia, a nie daty (szkoda, bo to rozwiązało by problem…).
Możliwe opcje
Tak wygląda kartka, na której rozrysowałam sobie wszystkie (rety – mam nadzieję!) opcje:
Możliwe opcje godzin nocnych
No właśnie… trochę tego jest. Przyznam, że opcja 5 zaznaczona na czerwono, to już mega wymysł i chyba nikt tak nie pracuje, ale jakby co, to jest…
Rozpatrzyłam następujące przypadki:
- Godziny pracy przekraczają północ –> na zdjęciu zaznaczone ołówkiem, na formatce czarne warianty 1-6,
- Godziny pracy nie przekraczają północy –> na zdjęciu zaznaczone czerwonym długopisem, na formatce czerwone warianty 1-6.
Te opcje w tabelce w Excelu wyglądają tak (formatka):
Możliwe opcje i formatka
No właśnie – jeśli chodzi o formatkę to też trochę ją zmieniłam versus poprzedni raz. Dodałam kolumny Przekroczenie północy, Nocne z przekroczeniem i Nocne bez przekroczenia. Usunęłam zaś obie kolumny robocze Rob od i Rob do.
A to dalsza część moich przemyśleń i pracy nad formułą – schemat liczenia godzin nocnych, czyli zagnieżdżanie JEŻELI (mam nadzieję, że coś widać – pisałam ołówkiem):
Schemat zagnieżdżania funkcji JEŻELI
Ok. No to czas na formuły.
Nazwane komórki
Aby ułatwić sobie pracę – nazwałam komórki. Komórka A2 to nocna_od, a komórka B2 to nocne_do. Aby nazwać komórki, należy zaznaczyć komórkę, którą chcesz nazwać, kliknąć na pole nazwy (lewy górny róg obok paska formuły) i wpisać wybraną nazwę. Zatwierdzamy oczywiście Enterem. Polecam skorzystać – formuły będą znacznie bardziej czytelne.
Formuły
Przekroczenie północy
To prościutka formułka, która sprawdza, czy godziny pracy przekroczyły północ. Będzie mi potem potrzebna. W komórce D6:
=B6>C6
Oczywiście działa ona wtedy, gdy spełnione są założenia!
Czas pracy
To już nieco bardziej skomplikowane, bo wchodzi nam JEŻELI. Ale spoko – to jeszcze pikuś ;). Komórka E6:
=C6+JEŻELI(D6;1;0)-B6
Nocne z przekroczeniem
I zaczynają się schody… :). Komórka F6:
=JEŻELI(ORAZ(B6<nocna_do;C6<nocna_do;C6<B6);nocna_do-B6+1-nocna_od+C6;JEŻELI(B6>=nocna_od;JEŻELI(C6<=nocna_do;1-B6+C6;1-B6+nocna_do);JEŻELI(C6<=nocna_do;1-nocna_od+C6;1-nocna_od+nocna_do)))
Nocne bez przekroczenia
I dalsze schody w komórce G6:
=MAX(0;JEŻELI(ORAZ(B6<nocna_do;C6<=nocna_do);C6-B6;JEŻELI(ORAZ(B6>nocna_od;C6>nocna_od);C6-B6;JEŻELI(B6>=nocna_do;JEŻELI(C6<=nocna_od;0;C6-nocna_od);JEŻELI(C6<=nocna_od;nocna_do-B6;nocna_do-B6+C6-nocna_od)))))
Nocne
I formułka wyliczająca godziny nocne – bierze odpowiednie godziny nocne: z przekroczeniem lub bez. Komórka H6:
=JEŻELI(D6;F6;G6)
Dzienne
I dzienne jako różnica godzin wszystkich i nocnych – prościzna w komórce I6:
=E6-H6
I wynik:
Wynik
Wow. Trochę tego jest, ale moim zdaniem jest znacznie czytelniej niż ostatnim razem. Dla chętnych jak zwykle – plik do pobrania. Dajcie znać w komentarzach, czy tym razem uwzględniłam wszystkie opcje 🙂 Mam nadzieję!
Plik do pobrania:
jak dla mnie w 3 czerwonym wariancie jest błąd (ale nie wnikałem dlaczego) bo patrząc po godzinach powinno być 7 nocnych i 1 dzienna (od 6 do 7 to już dzienna)… więc coś tu jeszcze jest nie tak :/
Dzięki za uwagę – już poprawiłam. Błąd był w trzecim jeżeli. Już jest ok: plik do pobrania też poprawiłam.
a jak dodatkowo wykonać, żeby było to czytelniejsze i zamiast zer, komórka była pusta? pozdrawiam
Polecam sformatować ją tak, aby zero nie było wyświetlane. Czyli w formatowaniu niestandardowym wpisz tak: “standardowy;standardowy;” (oczywiście bez cudzysłowów). Z wpisania zera bym nie rezygnowała.
Pozdrawiam
Malina
Dziękuję za ten artykuł 🙂 Ale mam problem bo u mnie w zakładzie są inne godziny nocne. W którym miejscu mogłabym to zmienić?
pozdrawiam
Nie ma tematu, już znalazłam 🙂
A czy arkusz przewiduje dobę jako dzień czy jako 24 godziny od rozpoczęcia pracy?
Nie, nie przewiduje.
Witam Malino.
Przeglądając większość Twoich rozwiązań – jestem pod wrażeniem. Z kilku już skorzystałem. Zgodnie z Twoją sugestią, mój problem przedstawiam tutaj. Chciałbym w nowej firmie uporządkować dokumenty a to co można – “zautomatyzować” (nie wiem, czy się to uda przy moim poziomie wiedzy “excelowej”). Ale do rzeczy : chodzi o kartę pracy pracownika. Jest ona w naszej firmie o tyle skomplikowana, że ze względu na potrzeby, pracownicy nie rozpoczynają pracy o sztywno ustalonych godzinach. Przychodzą do pracy, kiedy jest taka potrzeba. Rozliczani są za faktycznie przepracowany czas w okresie konkretnej zmiany. Na maila przesyłam fragment dotychczasowej karty pracy, która była przez mojego poprzednika wypełniana ręcznie (!). Może łatwiej zobrazuje to problem, z którym chciałbym dać sobie radę z Twoją pomocą. Czy masz jakieś sugestie ?
Witam,
wzór rozliczania godzin nocnych…rewelacja, wiele ułatwia, skracając jednocześnie czas mojej pracy :)Jeden szkopuł w tym, że czas pracy w naszym przypadku przekracza niekiedy 24 godziny ( zatrudniamy kierowców). Czy mogę liczyć na jakąś podpowiedź jak to “ugryźć”?
Hej,
w takim przypadku trzeba byłoby dodać jakiś znacznik, mówiący o tym, że 24h są przekroczone. Inaczej Excel nie zauważy różnicy ;( Może najlepiej wpisywać daty od i daty do?
Dziękuję za podpowiedź,zaraz zacznę kombinować 🙂
witam, nie czemu nie można otworzyć excela???
Dzień dobry!
Walczę z tabelą czasu pracy od jakiegoś czasu i napotkałem problem związany z powyższym tematem. Chodzi o nadgodziny w nocy. Np. jeśli pracuję od 14:00 do 23:00 to mam 1 nadgodzinę w czasie nocnym. Jak to wyłuskać? Uporałem się z wieloma zagwozdkami, tu jednak nie mogę skonstruować potrzebnej formuły.
Dzień dobry!
Oj, na ten temat to spokojnie można napisać podobny artykuł :). Mam proste rozwiązanie tego problemu natomiast jest strasznie niedoskonałe. Zadziała praktycznie tylko w opisywanym przez Pana przypadku: =JEŻELI(E6>Etat;JEŻELI(C6>nocna_od;E6-Etat;0)).
Gdzie Etat to np: 8:00, czyli godzina, w rozumieniu Excela; a pozostałe komórki to komórki z opisywanego przeze mnie arkusza. Proszę sobie wstawić tę formułę do komórki J6 i wyjdzie 🙂
Witam.
Bardzo mi sie podoba co zrobilas tylko brakuje mi sumy czasu pracy w dzien i czasu pracy w nocy. Bede wdzieczy jak napiszesz jak to zrobic poniewaz po sumowaniu wychodzi mi bzdura.
Hej,
bardzo się cieszę, że Ci się podoba :).
Odnośnie sumy: zrób zwykłą sumę (=SUMA(…)), natomiast, żeby wyświetlił się sensowny wynik, należy zmienić formatowanie, zgodnie ze wskazówkami z tego artykułu: Obliczanie czasu pracy: tygodniowy czas pracy i nadgodziny.
Mam nadzieję, że o to chodziło 🙂
Cześć. Plik do pobrania już chyba nie działa :-). Spróbuję pójść Twoim tokiem rozmowania, choć przy schodach straciłem wątek :-).
Hej,
a nie działa w sensie, że nie da się pobrać? Bo jeśli tak, to spróbuj prawym i “pobierz plik”. Choć powinno się pobierać… dziwne…
Witam
Jak już pisałem w emailu … jestem pod wrażeniem rozwiązania tego zagadnienia. Mam pytanie odnośnie nadgodziń dziennych i nocnych…. jak je obliczyć…szczególnie po północy. Do północy w przypadku pracy np 11:00-23:00 poradziłem sobie w ten sposób JEŻELI(ORAZ(Ilość_nadgodzin>0;godz_do>nocna_od;Przekroczenie_północy=FAŁSZ);godz_do-nocna_od;0)
Problem mam po północy gdzy np praca jest w godz 20:00-6:00, gdzie nocna_od=21:00 a nocna_do=5:00.
Występuje wtedy 10h , 8h pracy i dwie nadgodziny ( 1-dzienna i 1 nocna ) .
Jak mogę ugryźć ten problem ? : )
Hej,
już odpisałam na maila :).
Dorzuciłam trochę kolumn pomocniczych, żeby nie gmatwać formuły i coś tam wyszło. Mam nadzieję, że jest ok.
Jak z tak wyliczonych godzin obliczyć wynagrodzenie?
Najprościej pomnożyć czas pracy * wynagrodzenie * 24 🙂