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

Obliczanie czasu pracy: godziny nocne

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 pracy Koniec pracy Przykład
1. godziny dzienne godziny dzienne ten sam dzień 8:00-16:00
2. godziny dzienne godziny nocne ten sam dzień 18:00-23:00
3. godziny dzienne godziny nocne następny dzień 18:00-1:00
4. godziny nocne godziny dzienne następny dzień 21:30-7:00
5. godziny nocne północ 23:00-0:00
6. godziny nocne godziny 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

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.

20 odpowiedzi na „Obliczanie czasu pracy: godziny nocne

  1. Wiktor Chmielewski mówi:

    Piszesz, że będzie łatwiej w wypadku daty i godziny. Co się wtedy zmieni?

    • Malina mówi:

      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

  2. sesunial mówi:

    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

  3. Particulier mówi:

    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 ?

  4. Particulier mówi:

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

  5. Particulier mówi:

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

  6. Particulier mówi:

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

  7. wp mówi:

    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…

  8. Lukasz mówi:

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

  9. Tomasz mówi:

    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 ?

Dodaj komentarz

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