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.

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

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:

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:

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:

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