fbpx

Obliczanie czasu pracy: godziny nocne

13.11.2014 | Czas pracy, Daty i czas, ECP2, HR

Temat, który koniecznie trzeba poruszyć przy omawianiu czasu pracy, to godziny nocne. W tym artykule będzie nas interesowało:

  • ile godzin pracownik przepracował (ogólnie),
  • ile z nich przepracował w trybie dziennym,
  • a ile – w nocnym.

Uwaga! Ponieważ przedstawiona w tym wpisie formuła nie obsługiwała wszystkich możliwych przypadków – napisałam kolejny artykuł z formułą, która uwzględnia wszystkie przypadki. Zachęcam do zapoznania się z nim:

Obliczanie czasu pracy: godziny nocne REAKTYWACJA

 

Kasiu i Sebastianie, ten artykuł dedukuję Wam. Skutecznie zmotywowaliście mnie bowiem, bym przerzuciła go na początek kolejki i napisała jak najszybciej :). Myślę, że ten artykuł odpowie również na podobne pytania innych osób, np. Łukasza o nadgodziny w godzinach nocnych. Chętnie przeczytam w komentarzach, czy wam też i w jakim zakresie przydał się ten artykuł, także: śmiało komentujcie 🙂

Oto i on – artykuł dłuuugi i bardzo wyczekiwany!

Dane, które uzupełnia użytkownik, są bardzo proste: godzina rozpoczęcia i zakończenia pracy. Tak wygląda tabela danych (zakres A5:G23):

MalinowyExcel czas pracy godziny nocne: tabela danych

Tabela definicyjna i tabela danych

Jak widzimy, oprócz tabeli danych umieściłam tutaj również tabelę definicyjną: A1:B2. Są w niej godziny graniczne nocy: zaczyna się ona o 22:00, a kończy o 6:00 rano. Jeśli u was w pracy granice te są inne, zmieńcie je właśnie w tym miejscu.

Oczywiście kolumny do uzupełnienia to kolumny: godz. od (godzina rozpoczęcia pracy) i godz. do (godzina zakończenia pracy). Czas pracy może rozpoczynać się (godz. od) i kończyć (godz. do) tego samego dnia, może też przechodzić na dzień następny – wtedy mamy do czynienia z pracą w nocy. Do tabeli nie wpisujemy dat (a szkoda, bo wtedy sprawa byłaby znacznie łatwiejsza), jedynie godziny. Robimy tak dlatego, że domyślnie traktujemy godzinę zakończenia i rozpoczęcia pracy, jako pracę w tym samym dniu (na jednej zmienia). Nieważne, że zmiana wchodzi na kolejny dzień. Excel by to zrozumiał inaczej (nie tak, jak tego chcemy), stąd trzeba „bawić się” w pisanie formuły.Tak chcieliście 🙂

Możliwe przypadki

Nasza formuła musi być odporna na następujące sytuacje:

Lp.Początek pracyKoniec pracyPrzykład
1.godziny dziennegodziny dzienne ten sam dzień8:00-16:00
2.godziny dziennegodziny nocne ten sam dzień18:00-23:00
3.godziny dziennegodziny nocne następny dzień18:00-1:00
4.godziny nocnegodziny dzienne następny dzień21:30-7:00
5.godziny nocnepółnoc23:00-0:00
6.godziny nocnegodziny nocne następny dzień23:00-3:00
7.godziny nocne (po północy)godziny nocne ten sam dzień03:00-4:00
8.godziny nocne (po północy)godziny dzienne ten sam dzień03:00-8:00

Mam nadzieję, że pomyślałam o wszystkich możliwych przypadkach. W tabeli nie uwzględniłam sytuacji, w której pracownik pracuje na tyle długo, że zahacza o jeszcze kolejny dzień. Po pierwsze, rozumiem konieczność pracy w nadgodzinach, ale nikt nie powinien pracować tak długo jednym ciągiem. Po drugie, korzystając z mojego rozwiązania, można sobie z tym poradzić.

Formuła będzie korzystała z 2 kolumn roboczych (rob. od i rob. do) i z nazwanych komórek. Poniżej znajdują się szczegóły.

Nazwanie komórek

Ponieważ często będziemy się odwoływali do godzin granicznych znajdujących się w komórkach A2 i B2, nazwiemy te komórki. Aby to zrobić:

  1. Zaznacz komórkę A2.
  2. W polu nazwy (lewy górny róg ekranu, obok paska formuły) wpisz nazwę nocna_od i zatwierdź Enterem.
  3. Zaznacz komórkę B2.
  4. W polu nazwy wpisz nazwę nocna_do i zatwierdź Enterem.

Od tej pory, gdy będziemy potrzebowali odwołać się do którejkolwiek z tych dwóch komórek, w formule wpiszemy jej nazwę, czyli nocna_od lub nocna_do. Dzięki temu nasza formuła będzie przejrzysta, a co za tym idzie – zrozumiała.

Kolumny robocze

Aby uprościć formułę, oprócz nazw należy stworzyć kolumny robocze (na koniec oczywiście je ukryjemy). Wyświetlane w nich będą godziny z uwzględnieniem zakończenia dnia i rozpoczęcia kolejnego. Do każdej godziny po północy będzie doliczana liczba 24. Czyli np. jeśli pracownik zaczął pracę o 23:00, a skończył o 3:00, oznacza to, że pracował 3 godziny po północy. Gdyby więc numerować dalej godziny, godzina 3:00 byłaby godziną 27:00 (północ to 24:00, godz. 1:00 to 25:00, godz. 2:00 to 26:00, a godz. 3:00 to 27:00 itd).

W przypadku godzin rozpoczęcia pracy będziemy dodawać 24 godziny wtedy, gdy pracownik rozpocznie pracę po północy w godzinach nocnych (od 0:00 do 6:00). Opisuje to następująca formuła, którą należy wpisać do komórki C6 (kolumna rob. od):

=JEŻELI(A6<nocna_do;A6+1;A6)

Następnie formułę należy skopiować aż do komórki C23. Aby zachować formatowanie komórek, polecam skorzystać ze skrótu klawiszowego Ctrl + Enter (dokładny opis znajdziesz tutaj).

W przypadku godzin zakończenia pracy sytuacja jest nieco bardziej skomplikowana. Są bowiem 3 warianty, w których będziemy dodawać 24 do godziny wyjścia z pracy (w formule jest to po prostu 1):

  1. godzina zakończenia pracy jest mniejsza niż godzina rozpoczęcia pracy (pracownik zaczął pracę wieczorem poprzedniego dnia, a skończył rano następnego),
  2. godzina zakończenia pracy jest mniejsza niż godzina graniczna 6:00 (pracownik zakończył pracę w godzinach nocnych po północy),
  3. godzina rozpoczęcia pracy jest mniejsza niż godzina graniczna 23:00 (pracownik rozpoczął pracę w godzinach dziennych).

Warunki te sprawdzi następująca formuła:

=JEŻELI(LUB(B6<A6;B6<=nocna_do;A6<nocna_do);B6+1;B6)

Wpisz ją do komórki D6 w kolumnie rob. do, a następnie skopiuj aż do D23. Znów polecam skrót klawiszowy Ctrl + Enter (dokładny opis znajdziesz tutaj).

Zauważ, że w powyższych formułach do godzin dodaję 1 a nie 24 (jakby się mogło wydawać). Dzieje się tak dlatego, że 1 to są właśnie 24 godziny. Wszystko dlatego, że Excel traktuje czas jak ułamki dnia.

Żeby uzyskać efekt sumowania godzin, należy jeszcze odpowiednio sformatować dane. Zaznacz zakres C6:D23, w formatowaniu komórki na zakładce Liczby wybierz kategorię Niestandardowe. Wpisz tam następujący format: [gg]:mm (więcej o tym możesz przeczytać tutaj). Po zatwierdzeniu klawiszem OK, uzyskasz efekt widoczny na obrazku:

MalinowyExcel czas pracy godziny nocne: kolumny robocze

Uzupełnione i sformatowane kolumny robocze

Mamy już kolumny robocze, przejdźmy zatem do właściwych obliczeń.

Wyliczenie czasu pracy

To akurat najprostsza sprawa 🙂 Wystarczy odjąć godzinę rozpoczęcia pracy od godziny jej zakończenia. Skorzystamy tutaj z naszych kolumn roboczych, bo tylko tam uwzględnione jest ewentualne przejście na następny dzień.

Oto formuła, którą należy wpisać do komórki E6 i skopiować do E23:

=D6-C6

Wyliczenie liczby godzin nocnych

Tutaj już trudniej. Można powiedzieć, że to jest główna formuła. Sprawdza ona wszystkie przypadki opisane wyżej w tabeli. Korzysta oczywiście z nazwanych komórek i kolumn roboczych.

Formułę tę wpisz w komórce F6 i skopiuj aż do F23. Uwaga, oto ona…:

=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)))))

Wow, taka długa, że trzeba przewijać, bo nie zmieściła się w pasku formuły 😉

Wyliczenie liczby godzin dziennych

Na szczęście jak już wyliczyliśmy godziny ogółem i godziny nocne, to liczba godzin dziennych stanowi… po prostu ich różnicę :). Aby więc dalej nie komplikować (mogłabym ją liczyć tak jak w przypadku godzin nocnych), zwyczajnie odejmijmy te wartości od siebie.

W komórce G6 wpisz:

=E6-F6

a następnie skopiuj tę formułę aż do G23.

Oto wynik końcowy:

MalinowyExcel czas pracy godziny nocne: wynik

Tabela ze wszystkimi obliczeniami

Tadam!

 Ukrycie kolumn roboczych

Obiecałam, że ukryjemy kolumny robocze. Do dzieła:

  1. Zaznacz całe kolumny C i D.
  2. Kliknij prawym klawiszem myszy i z menu kontekstowego wybierz Ukryj lub użyj skrótu klawiszowego Ctrl + 0 (zero).

Wszystko 🙂

 

 

Redakcja językowa: Aleksandra Wasiak

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. Piszesz, że będzie łatwiej w wypadku daty i godziny. Co się wtedy zmieni?

    Reply
    • Chodziło mi o to, że jeśli w komórkach jest informacja o dacie i godzinie to wystarczy zastosować proste odejmowanie i otrzymujemy dokładną informację o czasie pracy.

      Pozdrawiam
      Malina

      Reply
      • Witam,
        chciałabym pociągnąć wątek. Mam 4 brygatówkę w pracy i w grafiku EX mam format godziny wraz z datą. Oraz czas pracy liczony z różnicy końca i rozpoczęcia. Niestety mam problem z zmodyfikowaniem formuły zliczenia godzin nocnych. Mogłabyś pomóc?
        Pozdrawiam

        Reply
        • Hej, wpiszę sobie na listę postów do napisania 🙂

          Reply
  2. Moje zapytanie dotyczy sumy godzin: Jaką formułę trzeba wpisać aby dodało godziny nocne i dzienne, bo jak wpisuję formułę np: =SUMA(D2:D32) to mi źle sumuje!?
    Proszę o radę!

    Pozdrawiam
    sesunial

    Reply
  3. A czy jest to gdzieś do pobrania, bo powiem szczerze ,że najlepiej i najszybciej kumam formuły i przerabiam po swojemu gdy widzę je w excelu ?

    Reply
    • Tak, już jest po artykułem.

      Reply
  4. Dziękuje bardzo 🙂

    P.S Jak sumować czas pracy i godziny nocne w przykładzie kolumna E i kolumna F (u mnie to inne kolumny) ?

    Myślałem ,że prosta formuła SUMA sobie poradzi: =SUMA(F6:F36) i format komórki niestandardowe :[g]:mm

    Jednak pokazuje głupoty.
    Przykład:
    Czas pracy 4:45 , 7:00 , 7:00 , 7:00 , 8:00 , 16:00 , 3:00
    Sumuje jako: 725:45 ,a powinno wyjść: 52:45.

    Proszę o poradę.

    Reply
  5. Może rozwiązaniem jest dodanie dodatkowej kolumny (ukrytej) obok kolumny czas pracy gdzie zapisywało by czas jako liczbę i z tej kolumny to sumować ?

    Reply
  6. Wiem co problemem jest.
    W kolumnie F mam formułę: E7 – B7, czyli 33 godz – 2 godz = 1 dzień i 7 godz. Excel pokazuje tylko 7 godz ,a w domyśle mam 31 godz. Dlatego pokazuje taką sumę godzin.

    Formuła liczenia czasu pracy jest do poprawienia od początku i wiem już jak :).

    Reply
    • Super, że już sobie poradziłeś – przez weekend nie otwierałam komputera 🙂

      Pozdrawiam
      Malina

      Reply
  7. Jaką funkcję użyjemy gdy ktoś zaczyna pracę w godzinach nocnych, kończy w dziennych ale z racji tego że zaczyna w nocnych, cały dzień liczony jest jako godziny nocne…

    Reply
  8. Witam. Jestem pod wrazeniem wiedzy. Jednak mam pytanko jeśli pracuję od 6 do 23 czyli 17 godzin to w kolumnie F excel pokazuje wartość 0 zamiast 1. Mogę prosić o wskazówkę.

    Reply
    • Ooo, faktycznie – muszę pomyśleć, o co chodzi…

      Reply
      • Będę wdzięczny, ja też kombinuję.

        Reply
      • I jak jest już pomysł?

        Reply
  9. Witam, mam pytanie do Autorki, formuła działa świetnie ale tylko na godzinach sformatowanych w trybie czasowym. Ja potrzebuję aby formuła zliczała liczbę godzin ale z formatu liczbowego, np w jednej kolumnie 7 w drugiej 15. daje 8 rbg. Później jak wpiszę 22 i 6 to zwróci wynik 8. Czy jest możliwa taka konwersja ?

    Reply
    • Hej, tak, możesz każdą z tych godzin zmodyfikować wg wzoru: 8 -> 8/24. W formule liczącej godziny pracy oczywiście.
      BTW: Polecam Ci zajrzeć do uaktualnionego wpisu: Godziny nocne – reaktywacja.

      Reply
  10. Dzień dobry, u mnie przy niektórych wyliczeniach godzin nocnych pojawia się zamiast godziny ###### nie przy wszystkich pozycjach np. gdy pracownik zaczyna prace o 0:00. Pozdrawiam

    Reply
  11. a możesz podpowiedzieć jak z tego dodatkowo liczyć nadgodziny 50 i 100%?

    Reply
  12. wszystko super tylko mogłabyś jeszcze „dopieścić” o opcje weekendu 🙂
    Generalnie czasem zdarza sie ze pomimo pracy pon-pt trzeba przyjść w weekend do pracy a wtedy nawet godzina dzienna jest liczona +100% czyli jako nocna.
    Moznaby dodac kolumne WEEKEND z opcja do wyboru TAK/NIE
    w przypadku zaznaczenia NIE liczyłoby tak jak do tej pory natomiast w przypadku opcji TAK każda godzina liczona bylaby jako nocna

    Reply
    • Hehe, faktycznie:). Zaproponowałeś super opcję! Najprostszą z możliwych – uwielbiam takie :). Jedno tylko nie daje mi spokoju: a co w sytuacji, gdy ktoś przychodzi do pracy piątek/sobota, albo niedziela/poniedziałek?

      Reply

Submit a Comment

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

Pin It on Pinterest