• Zapisz się na newsletter i odbierz DARMOWY EBOOK: 10 najprzydatniejszych porad excelowych

Obliczanie czasu pracy: godziny nocne REAKTYWACJA

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

 

Spodobał Ci się ten artykuł?

Jeśli TAK, zapisz się na newsletter, aby otrzymywać powiadomienia
o najnowszych wpisach (i tylko powiadomienia: zero spamu).
Dodatkowo otrzymasz DARMOWY EBOOK pt.:

10 najprzydatniejszych porad excelowych


Tagi , , , , , , , , , .Dodaj do zakładek Link.

12 odpowiedzi na „Obliczanie czasu pracy: godziny nocne REAKTYWACJA

  1. Bartek mówi:

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

  2. Jacek mówi:

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

    • Malina mówi:

      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

  3. Ewa mówi:

    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

  4. zefirek mówi:

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

  5. Grzegorz mówi:

    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 ?

  6. Jola mówi:

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

    • Malina mówi:

      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?

  7. Jola mówi:

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

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *