fbpx

Obliczanie czasu pracy: godziny nocne REAKTYWACJA

22.09.2016 | Czas pracy, Daty i czas, ECP2, 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ę!

 

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

28 komentarzy

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

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

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

    Reply
    • 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

      Reply
  3. 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

    Reply
    • Nie ma tematu, już znalazłam 🙂

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

    Reply
    • Nie, nie przewiduje.

      Reply
  5. 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 ?

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

    Reply
    • 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?

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

    Reply
  8. witam, nie czemu nie można otworzyć excela???

    Reply
  9. 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.

    Reply
    • 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 🙂

      Reply
  10. 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.

    Reply
    • 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 🙂

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

    Reply
    • 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…

      Reply
  12. 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 ? : )

    Reply
    • 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.

      Reply
  13. Jak z tak wyliczonych godzin obliczyć wynagrodzenie?

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

      Reply
  14. 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.

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

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

      Reply
  16. 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.

    Reply
  17. 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.

    Reply

Leave a Reply to Wojtek Anuluj pisanie odpowiedzi

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

Pin It on Pinterest