fbpx

Obliczanie czasu pracy: godziny nocne REAKTYWACJA

22.09.2016 | Czas pracy, Daty i czas, HR

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:

  1. Pracownik pracuje krócej niż 24 godziny, czyli:
  2. Godzina wejścia i godzina wyjścia nie mogą się sobie równać.
  3. 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

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:

  1. Godziny pracy przekraczają północ –> na zdjęciu zaznaczone ołówkiem, na formatce czarne warianty 1-6,
  2. 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

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 przekroczeniemNocne 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

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

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ę!

 

Zapisz się na bezpłatny mailing!

Zapisz się na bezpłatny mailing i otrzymaj bezpłatny e-book „10 najprzydatniejszych trików w Excelu”!

Newsletter Malinowy Excel | Bezpłatny e-book
Przeczytaj podobne wpisy

Kategorie

28 komentarzy

  1. Bartek

    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 :/

    Odpowiedz
    • Malina

      Dzięki za uwagę – już poprawiłam. Błąd był w trzecim jeżeli. Już jest ok: plik do pobrania też poprawiłam.

      Odpowiedz
  2. Jacek

    a jak dodatkowo wykonać, żeby było to czytelniejsze i zamiast zer, komórka była pusta? pozdrawiam

    Odpowiedz
    • Malina

      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

      Odpowiedz
  3. Ewa

    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

    Odpowiedz
    • Ewa

      Nie ma tematu, już znalazłam 🙂

      Odpowiedz
  4. zefirek

    A czy arkusz przewiduje dobę jako dzień czy jako 24 godziny od rozpoczęcia pracy?

    Odpowiedz
    • Malina

      Nie, nie przewiduje.

      Odpowiedz
  5. Grzegorz

    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 ?

    Odpowiedz
  6. Jola

    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źć”?

    Odpowiedz
    • Malina

      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?

      Odpowiedz
  7. Jola

    Dziękuję za podpowiedź,zaraz zacznę kombinować 🙂

    Odpowiedz
  8. klaudia

    witam, nie czemu nie można otworzyć excela???

    Odpowiedz
  9. Krzysztof

    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.

    Odpowiedz
    • Malina

      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 🙂

      Odpowiedz
  10. Marcin Strzyzewski

    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.

    Odpowiedz
    • Malina

      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 🙂

      Odpowiedz
  11. Adam

    Cześć. Plik do pobrania już chyba nie działa :-). Spróbuję pójść Twoim tokiem rozmowania, choć przy schodach straciłem wątek :-).

    Odpowiedz
    • Malina

      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…

      Odpowiedz
  12. Arek

    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 ? : )

    Odpowiedz
    • Malina

      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.

      Odpowiedz
  13. Sylwia

    Jak z tak wyliczonych godzin obliczyć wynagrodzenie?

    Odpowiedz
    • Malina

      Najprościej pomnożyć czas pracy * wynagrodzenie * 24 🙂

      Odpowiedz
  14. Anciak

    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.

    Odpowiedz
  15. Wojtek

    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….???

    Odpowiedz
    • Malina

      To prawda, odbiór nadgodzin to kolejny temat do uwzględnienia.

      Odpowiedz
  16. Andrzej

    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.

    Odpowiedz
  17. Masiek

    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.

    Odpowiedz

Odpowiedz Grzegorz Anuluj pisanie odpowiedzi

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

Pin It on Pinterest