Sumowanie godzin pracy w Excelu – ewidencja czasu zmian krok po kroku

Sumowanie godzin pracy w Excelu może wydawać się proste, dopóki grafik nie obejmie zmiany nocnej, przerwy albo kilku tygodni pracy. Wtedy zamiast wyniku pojawiają się krzyżyki, suma godzin zeruje się po przekroczeniu doby, a wpisane „30” może oznaczać 30 dni, nie 30 minut. Sprawdź krok po kroku, jak funkcją mod poprawnie obliczyć czas zmian przechodzących przez północ, odjąć przerwy, wyświetlić miesięczną sumę w formacie [h]:m i zamienić ją na godziny dziesiętne potrzebne do rozliczeń.

Ciąg krzyżyków zamiast godzin – skąd bierze się ujemny czas zmiany nocnej

Godzina w Excelu to w istocie ułamek doby – 6:00 rano to 0,25, 18:00 to 0,75. Przy zmianie dziennej, gdy początek i koniec mieszczą się w tej samej dobie, zwykłe odejmowanie Koniec minus Początek liczy się bez problemu. Zmiana nocna łamie tę logikę: pracownik zaczyna o 22:00, kończy o 6:00, a 6:00 jako ułamek doby jest liczbowo mniejsze niż 22:00. Wynik odejmowania wychodzi ujemny, a Excel nie potrafi wyświetlić ujemnego czasu w zwykłym formacie godzinowym – pokazuje serię krzyżyków, dopóki nie zmienisz formatu komórki, i wtedy zamiast krzyżyków zobaczysz liczbę ujemną, równie nieprzydatną do dalszych obliczeń.

Część osób planujących grafik obchodzi ten problem, wpisując pełne daty przy każdej godzinie: 14.03.2024 22:00 jako początek, 15.03.2024 6:00 jako koniec. Różnica dat liczy się wtedy poprawnie, bo Excel widzi przejście przez północ wprost w danych. Przy grafiku na kilkanaście osób i kilka tygodni do przodu wpisywanie pełnej daty do każdej komórki zamienia prosty arkusz zmianowy w benedyktyńską pracę, a jedna pomyłka w dacie psuje sumę bez żadnego ostrzeżenia na ekranie. Wygodniej jest zostawić w kolumnach same godziny i użyć funkcji MOD: zapis MOD(Koniec-Początek;1) zwraca resztę z dzielenia przez 1, czyli przez pełną dobę. Gdy odejmowanie Koniec-Początek wychodzi na minus, MOD dolicza brakującą dobę i zwraca dodatni wynik zgodny z rzeczywistą długością zmiany – dla 22:00–6:00 różnica to -16/24, a MOD(-16/24;1) daje 8/24, czyli osiem godzin.

Format komórki decyduje, czy zobaczysz godziny, czy dziwną liczbę

MOD zwraca ułamek doby, nie gotowy zapis godzinowy – bez odpowiedniego formatu komórka pokaże 0,3333 zamiast 8:00. Standardowy format gg:mm z listy wbudowanych formatów Excela wystarcza do pojedynczej zmiany, jeśli zależy ci na konkretnym wyglądzie kolumny. Kłopot z przekroczeniem doby wraca dopiero na etapie sumy wielodniowej, o czym w dalszej części.

Przerwa w minutach, nie w formacie czasu – tu psuje się druga połowa arkusza

Sama długość zmiany brutto to nie jest jeszcze liczba, którą można wpisać na listę obecności. Trzeba odjąć przerwę, a tu czai się druga pułapka, mniej oczywista niż krzyżyki przy zmianie nocnej. Osoba wypełniająca grafik zwykle wpisuje przerwę jako zwykłą liczbę – 30, mając na myśli 30 minut. Excel tego nie wie. W komórce sformatowanej jako czas liczba 30 oznacza 30 dni, więc odjęta od wyniku zmiany daje albo absurdalną wartość ujemną, albo kolejny rząd krzyżyków, zależnie od formatu kolumny.

Żeby przerwa podana jako liczba całkowita minut weszła do tej samej skali co reszta obliczeń, trzeba ją przeliczyć na ułamek doby. Doba liczy 1440 minut, więc pełna formuła wygląda tak: MOD(Koniec-Początek;1) – Przerwa/1440. Dla zmiany 22:00–6:00 z 30-minutową przerwą wynik to 8/24 minus 30/1440, czyli 7 godzin 30 minut netto. Kto nie chce dzielić przez 1440 w głowie, może użyć funkcji CZAS – zapis CZAS(0;Przerwa;0) daje dokładnie tę samą wartość, tylko w formie czytelnej wprost: zero godzin, tyle minut, zero sekund.

Suma zmian w arkuszu miesięcznym – dlaczego zwykły format czasu znowu zawodzi

Poprawnie policzona pojedyncza zmiana to dopiero jedna komórka w kolumnie. Miesięczna ewidencja zbiera dziesiątki takich wyników w jedną sumę – i tu wraca ten sam mechanizm, który wcześniej psuł zmianę nocną, tylko w większej skali. Standardowy format czasu w Excelu zeruje się po przekroczeniu 24 godzin, dokładnie jak wskazówki zegara po pełnym obrocie. Formuła SUMA na kolumnie dziennych czasów pracy liczy poprawnie pod spodem, ale wyświetlona w zwykłym formacie gg:mm pokaże resztę z dzielenia przez dobę zamiast pełnej liczby godzin w miesiącu – 186 godzin wyjdzie jako 18:00, bo Excel odjął pełne doby tak, jakby liczył dni na tarczy zegara.

Rozwiązanie to format niestandardowy z godziną w nawiasach kwadratowych: [h]:mm. Nawiasy każą Excelowi nie zawijać godzin po 24 – liczba rośnie w nieskończoność zamiast wracać do zera. Komórka z sumą miesięczną sformatowana jako [h]:mm pokaże 186:00 zamiast 18:00, i dopiero tę wartość warto przekazywać dalej do rozliczenia. Ten sam mechanizm sumowania warto zobaczyć też przy liczeniu dni roboczych od zera – opisuje to tekst o tym, jak obliczyć w Excelu liczbę przepracowanych godzin.

Zamiana skumulowanego czasu na dziesiętny system rozliczeniowy

Format [h]:mm sprawdza się w samym arkuszu ewidencji, ale systemy kadrowo-płacowe i większość arkuszy naliczających wynagrodzenie oczekują liczby dziesiętnej – 7 godzin 30 minut jako 7,5, nie jako zapis czasowy. Skoro Excel trzyma czas jako ułamek doby, przejście na godziny dziesiętne to zwykłe mnożenie przez 24: wartość z komórki w formacie [h]:mm pomnożona przez 24 daje liczbę godzin gotową do wstawienia w arkusz płacowy, pod warunkiem że wynik sformatujesz jako liczbę, nie jako czas.

Ostatni krok to zaokrąglenie do jednostki przyjętej w rozliczeniach, zwykle kwadransa. Zwykłe ZAOKR nie trzyma się kwadransów – zaokrągla do dowolnej liczby miejsc po przecinku, nie do wielokrotności 0,25. Do tego służy ZAOKR.DO.WIELOKR, która dopasowuje wynik do najbliższej wielokrotności podanej wartości. Zapis ZAOKR.DO.WIELOKR(GodzinyDziesiętne;0,25) sprowadza 7,58 godziny do 7,5, a 7,63 do 7,75 – zgodnie z rozliczaniem czasu pracy w blokach kwadransowych, które stosuje większość działów kadr przy naliczaniu nadgodzin. Tę samą funkcję da się zastosować jeszcze przed przejściem na system dziesiętny, bezpośrednio na zapisie godzinowym: ZAOKR.DO.WIELOKR(Czas;”0:15″) zaokrągla do pełnych piętnastu minut, zanim dojdzie do mnożenia przez 24. Która kolejność – najpierw zaokrąglenie, potem zamiana, czy odwrotnie – zależy wyłącznie od tego, na jakim etapie arkusz przekazuje dane dalej, i obie ścieżki dają ten sam wynik końcowy, jeśli trzymasz się jednej z nich konsekwentnie w całym arkuszu.