Excel i dni robocze – no bo przecież obiecałem

by Marcin

Zgodnie z obietnicą dzisiaj kilka słów o formule

={SUMA(ZAOKR.DO.CAŁK((DZIEŃ.TYG(C3-{2\3\4\5\6})+C4-C3)/7))}.

Z przyzwyczajenia zawsze wprowadzam ją jako formułę tablicową, wydaje mi się jednak (a i dotychczasowa praktyka na to wskazuje), że nic nie stoi na przeszkodzie, aby działała także jako zwykła formuła nietablicowa.

Jest ona rozwinięciem nieco prostszej formuły, która wygląda następująco:

= ZAOKR.DO.CAŁK((DZIEŃ.TYG(data_poczatek-1)+data_koniec – data_poczatek)/7)

i zwraca liczbę niedziel w określonym przedziale czasowym.

Sposób działania formuły najlepiej prześledzić na przykładzie.

Załóżmy, że chcesz sprawdzić ilość niedziel pomiędzy 7 i 22 października 2007 (włącznie z dniem pierwszym i ostatnim). Zakres czasowy obejmuje dwa tygodnie (na pewno będziesz więc miał przynajmniej dwie niedziele) plus jeden dzień.

O całkowitej liczbie niedziel zadecyduje ten dodatkowy dzień właśnie. Jeżeli jest to niedziela jako wynik otrzymasz liczbę 3. W każdym innym przypadku wynikiem będą tylko 2 niedziele. Jak mieć pewność, że dzień jest niedzielą?

data_koniec – data_poczatek zwraca liczbę dni w zadanym przedziale czasowym pomniejszoną o jeden (przy założeniu, że dzień pierwszy i ostatni wliczane są do kalkulacji). W naszym przypadku jest to 14 dni, co dzielone przez 7 daje oczywiście w wyniku 2 (niedziele). W nawiasie, którego zawartość dzielona jest przez 7, do różnicy data_koniec – data_poczatek dodawana jest jednak dodatkowo liczba zwracana przez DZIEŃ.TYG(data_poczatek-1). Ostatecznie, aby otrzymać wynik większy od 2 liczba ta powinna wynosić przynajmniej 7.

Dla Excela tydzień zaczyna się od niedzieli, której przypisany jest numer 1. Cofając się od niedzieli wstecz o jeden dzień tygodnia trafisz na ostatni dzień poprzedniego tygodnia – sobotę. Jeżeli więc 7 października jest niedzielą to funkcja DZIEŃ.TYG(data_poczatek-1) zwróci 7 (wartość liczbowa odpowiadająca sobocie). Wartość w nawiasie wyniesie 21, co dzielone przez 7 da nam w wyniku trzy niedziele.

Moje wyjaśnienie być może nie jest wyjaśnieniem najlepszym, chociaż mi wystarcza. To nie ja jestem autorem formuły i sam zastanowić się musiałem, dlaczego „to” działa.
Jeżeli rozumiesz wszystko inaczej lub  potrafisz opisać to lepiej – zapraszam do komentowania.

{ 4 comments… read them below or add one }

tomasz78

A nie lepiej było użyć funkcji WORKDAYS z tym że jest jedno ale. W tej funkcji używa się listy dni wolnych od pracy zawartych np. w osobnym arkuszu, ponieważ standardowo funkcja traktuje tylko niedzielę jako dzień wolny od pracy.

Marcin

Wydaje mi się, że niekoniecznie. Funkcja WORKDAY nie służy zasadniczo do obliczania ilości dni roboczych pomiędzy dwiema datami. Przyglądając się składni możesz zobaczyć, że jako parametr podajesz datę początkową oraz ilość dni roboczych. W wyniku działania funkcja zwróci Ci datę „oddaloną” od daty początkowej o podaną liczbę dni roboczych (uwzględniając ewentualnie święta). Idealnie nadaje się więc do sprawdzenia jaką datę będziemy mieli, powiedzmy 5 dni roboczych od daty dzisiejszej (możemy się przesuwać zarówno do przodu jak i do tyłu po kalendarzu).

Przykład:

Jeżeli w komórce A1 mam datę 2007 – 11 – 06 (w formacie daty) i wiem, że muszę przepracować 10 dni roboczych zanim będę mógł prosić o urlop, funkcją =Workday(A1;10) łatwo sprawdzę dzień kiedy będę mógł o ten urlop prosić – 20 listopada.

Jako dni wolne od pracy funkcja traktuje soboty i niedziele. Plus, ewentualnie, listę dodatkowych świąt (zupełnie jak NETWORKDAYS).

MAciej

Niestety powyższa formuła jest jedynie właściwa przy założeniu że między datami jest sobota i niedziela. W jednym tygodniu różnica w dniach roboczych jest o 2 mniejsza niż powinna być. Trzeba by dodać formułę warunkową „jeżeli” i uwarunkować to od rzeczywistej sytuacji.

tasiek

ja mam takie pytanie do was mam zrobic ewidencje urlopow wypoczynkowych. czym byscie to polecali zrobic jaka komenda albo jakies sugestie. pozdrawiam

Leave a Comment

Previous post:

Next post: