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!
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 🙂
Mam mały problem i zastanawiam się czy można go w jakiś sposób rozwiązać. Mianowicie chodzi o to, że chciałabym by excel liczył mi datę od godziny 6:00 do godziny 6:00 następnego dnia. Czyli 22.11.2021 obecnie zaczyna się o godzinie 00:00 i trwa do godziny 24:00 dnia 22.11.2021, a chciałabym by dzień 22.11.2021 zaczynał się o godzinie 06:00 i trwał do godziny 06:00 ale już dnia 23.11.2021. Ma to związek z trybem pracy u nas w firmie (3 zmiany: I 06:00-14:00, II 14:00-22:00, III 22:00-06:00 dnia następnego) i przedstawianiem różnego rodzaju raportów. U nas na 1 dzień składają się te 3 ww. zmiany i problem właśnie polega na tym, że kiedy chcę przedstawić dane nt. postojów linii za dany dzień to excel zlicza mi tylko postoje, które miały miejsce 22.11.2021 do godziny 24:00. Podejrzewam, że konieczna będzie zmiana w całym systemie operacyjnym jednak tu również prosiłabym o wskazówki jeśli to jest rozwiązaniem.
Będę bardzo wdzięczna za wszelką formę pomocy.
Bardzo ciekawe rozwiązania.
Jednak nie widzę tutaj rozwiązania problemu obliczania czasu pracy gdy pracownik chce wolne w zamian za przepracowane nadgodziny. Samo w sobie nie jest to skomplikowane – należy od sumy godzin nadliczbowych odjąć ilość wykorzystanych godzin i już !
Jak to obliczyć gdy pojawiają się godziny nocne….???
To prawda, odbiór nadgodzin to kolejny temat do uwzględnienia.
Witam.
Świetna jest ta formuła do obliczenia godzin nocnych, sprawdza się idealnie.
Chociaż minęło już sporo czasu od ostatniej aktywności w tym temacie to mam pytanie jak mam rozwiązać swój problem związany z tym zagadnieniem. W moim przypadku muszę w tabeli godzin pracy pracowników uwzględnić ogólny czas pracy, czas pracy w godzinach nocnych i dodatkowo czas pracy w godzinach popołudniowych. Z pierwszymi dwoma nie ma problemu (nocne dzięki Pani formule obliczane są idealnie) ale niestety godziny popołudniowe które zaczynają się od 14:00 do 22:00 już nie jest tak kolorowo.
Stworzyłem sobie dodatkowe odwołanie pop_do (22:00) i pop_od (14:00), podmieniłem w formule nocne_do i nocne_od na popołudniowe i obleczenia są poprawne do momentu gdy czas pracy przekracza północ.
Jak oprócz godzin nocnych obliczyć dodatkowo ilość godzin popołudniowych…??
Będę bardzo wdzięczny za wszelką formę pomocy.
A ja mam problem z obliczeniem prawidłowym godzin pracy gdy czas jest równy dokładnie 24h, mamy w firmie zmiany 12 i 24-godzinne, więc pracownik zaczyna np. 6.00 i kończy 6:00 dnia następnego. Ta formuła tego nie uwzględnia i zeruje czas pracy, pokazuje zero.