fbpx

Nadgodziny w tygodniu i w weekendy

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

Czyli trochę o wykorzystaniu funkcji DZIEŃ.TYG

Załóżmy, że pracownik pracuje na cały etat, czyli codziennie ma do przepracowania 8 godzin. My chcemy obliczyć liczbę nadgodzin, które zrobił. Nadgodziny rozumiem tutaj tak, że jeśli pracował w tygodniu dłużej niż 8 godzin, to każda nadwyżka to już są nadgodziny. Natomiast jeśli przyszedł do pracy w weekend, to każdy przepracowany czas traktuję jako nadgodziny.

Sytuację tę pokazuje poniższy obrazek, który równocześnie jest tym, co chcemy uzyskać.

Wynik

Pusta formatka wygląda więc tak:

Formatka

Formatka

Do komórki E3 trzeba wpisać wymiar etatu w postaci godzin, jakie pracownik powinien codziennie przepracować. Kolumna Czas pracy będzie zawierała faktycznie przepracowany czas, a kolumna Nadliczbowe – nadgodziny.  Ostatnia kolumna – Odebrane – informuje o tym, ile godzin danego dnia pracownik odebrał z nadgodzin (taka sytuacja jest np.  trzeciego dnia).

Omówię teraz po kolei wszystkie obliczenia.

Obliczanie czasu pracy

Zacznijmy od najłatwiejszego, czyli po prostu od obliczenia ile czasu pracownik w ogóle danego dnia przepracował. Jest to proste działanie: od czasu końcowego odejmujemy czas początkowy. Formuła w komórce E6 wygląda tak:

=D6-C6

A po skopiowaniu jej do pozostałych komórek kolumny Czas pracy dostajemy to:

Obliczony czas pracy

Obliczony czas pracy

Teraz główny punkt programu, czyli obliczanie nadgodzin…

Obliczanie nadgodzin w ciągu dnia

Zacznę od środka, czyli od omówienia funkcji, która umie rozpoznać dzień tygodnia. Funkcją, którą mam na myśli jest DZIEŃ.TYG. Funkcja ta zwraca dzień tygodnia w postaci cyfry. Ma ona dwa argumenty:

  1. data, z której chcemy poznać dzień tygodnia
  2. sposób prezentacji wyniku.

Drugi argument jest tutaj tajemniczy i zarazem kluczowy. Mówi on bowiem o tym, jaka cyfra ma oznaczać jaki dzień tygodnia. My oczywiście chcemy, aby poniedziałek był reprezentowany przez 1, wtorek przez 2 itd. Aby to uzyskać, w drugim argumencie funkcji DZIEŃ.TYG musimy wpisać cyfrę 2. Czyli funkcja będzie wyglądała tak:

DZIEŃ.TYG(B6;2)

I teraz jeśli ten dzień tygodnia będzie sobotą lub niedzielą (6 lub 7, czyli po prostu większe lub równe 6), to w komórce wynikowej chcemy wyświetlać czas pracy, wyliczony wcześniej. Za ten fragment rozumowania odpowiada następująca część formuły:

DZIEŃ.TYG(B6;2)>=6

Natomiast jeśli dzień tygodnia jest inny niż sobota lub niedziela – będziemy chcieli wyświetlić nadgodziny wyliczone poprzez odejmowania wymiaru etatu (8:00) od czasu pracy. Jeśli różnica będzie dodatnia -mamy nadgodziny. Jeśli nie – nie ma nadgodzin, czyli 0.

Do wybrania czy wyświetlić różnicę (nasze nadgodziny), czy też zero, można by użyć funkcji JEŻELI. Natomiast ja jej w tym użyciu nie lubię, bo nie lubię się powtarzać. Zastosuję natomiast funkcję MAX (ten sam trik omawiałam tutaj, przy liczeniu podatku i kosztów uzyskania przychodów). Będzie wyglądała ona następująco:

MAX(E6-$E$3;0)

Zadaniem funkcji MAX jest wybranie największej wartości spośród tych, które dostała w argumentach. Nasza MAX dostała różnicę (czas pracy minus etat) i zero. Jeśli będą nadgodziny – różnica będzie dodatnia. Jeśli nie będzie nadgodzin – zero lub ujemna. Czyli jeśli MAX dostanie coś takiego: MAX(1;0), to wybierze większą, czyli 1, czyli jedną nadgodzinę. natomiast jak dostanie coś takiego MAX(-1;0), to wybierze 0, czyli brak nadgodzin. I to cały myk.

Po wrzuceniu wszystkiego do funkcji JEŻELI, otrzymamy taką formułę:

=JEŻELI(DZIEŃ.TYG(B6;2)>=6;E6;MAX(E6-$E$3;0))

Obliczanie godzin odebranych

Tutaj znów zastosujemy myk z funkcją MAX. Tym razem będziemy badać różnicę między wymiarem etatu (8:00), a liczbą godzin przepracowanych danego dnia. Jeśli różnica ta będzie dodatnia (>0), tzn., że pracownik tego dnia odbierał nadgodziny. Jeśli zerowa lub ujemna – nie odbierał. Wynik różnicy porównamy funkcją MAX do zera i znów otrzymamy większą z wartości: zero w przypadku, gdy nadgodzin nie odbierano, a liczbę godzin w przypadku odbierania.

Funkcja jest taka:

=MAX(0;$E$3-E6)

Obliczanie wszystkich nadgodzin

Na koniec zostaje tylko obliczenie wszystkich nadgodzin. Będzie to po prostu różnica między godzinami nadliczbowymi, które nam wyszły z podsumowania kolumny Nadliczbowe, a podsumowaniem kolumny Odebrane. Formuła w komórce G16 jest taka:

=F14-G14

Po wpisaniu wszystkich omówionych formuł otrzymamy taki efekt:

Wynik

Wynik

I tyle. Jakże proste i użyteczne 🙂 Jak Ci się przyda – udostępnij to proszę znajomym, może im też się przyda.

Na koniec jeszcze wideo, w którym pokazuję krok po kroku jak zrobić omawiany w tym wpisie przykład:

I na koniec plik do pobrania:

Jeśli nie udaje Ci się pobrać pliku, kliknij na link prawym przycikiem myszy i wybierz opcję: Zapisz link jako”:

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

2 komentarze

  1. Jak zawsze Malina nie zawodzi.
    Ale tak sobie pomyślałem „no dobrze wszystko ok. liczy ładnie nam formuła w zależności od dnia tygodnia, ale co ze świętami ustawowymi?” Dodałem więc tabele z takimi dniami i dopisałem dodatkowy warunek z funkcją lub LUB(LICZ.JEŻELI(’zakres dni wolnych ustawowych;$B6);DZIEŃ.TYG($B6;2)>=6). Czyli coś takiego =JEŻELI(LUB(LICZ.JEŻELI(’Dni wolne’!$A$1:$A$12;$B6);DZIEŃ.TYG($B6;2)>=6);E6;MAX(E6-$E$3;0)) dla nadgodzin – tylko trzeba pamiętać co roku zmienić rok na aktualny (tez jest formuła na aktualny rok ściągany z sytemu) oraz że kilka terminów jest ruchomych np. Wielkanoc
    Często też zamiast wpisywać godziny w danym dniu wpisuje np. delegacja lub urlop no i mogiła bo wyskakuje argument #ARG co z tym zrobić? wystarczy to =D6-C6 zamienić na to =JEŻELI.BŁĄD(SUMA(D6-C6);0) co spowoduje że każdy błąd podstawowej formuły będzie wynosił „zero” i powinno działać.
    Pozdrawiam

    Reply
    • Hej 🙂
      Cieszę się, że mogłam pomóc!
      W takiej sytuacji zrobiłabym 2 dodatkowe kolumny-znaczniki:
      1. Święto – tutaj formuła decyduje, czy dzień jest świąteczny i wyświetla stosowny komentarz. Wykorzystuje tabelę ze świętami, którą też trzeba zdefiniować (a w niej dorzucić wszuelkie dni wolne, nawet dni odbierane za święta w soboty)
      2. Urlop/delegacja

      I na podstawie tych 2 kolumn działać logiką, tak jak Ty zrobiłeś

      Reply

Submit a Comment

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

Pin It on Pinterest