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

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

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

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