Dzisiaj będzie rozbudowanie tematu z poprzedniego wpisu, w którym opisywałam jak poznać datę np. pierwszego czwartku miesiąca. Wykorzystałam do tego dość skomplikowaną formułę (ale formułę! nie makro ;)), zawierającą aż 4 funkcje. Dziś sobie to rozszerzymy, ponieważ założenia są takie:

W naszej firmie przelewy wychodzą tylko w pierwszy albo trzeci czwartek miesiąca. Jeśli faktura się „nie załapie” na pierwszy czwartek – jest płacona w trzeci czwartek, jeśli na niego się „nie załapie: – dopiero w pierwszy czwartek następnego miesiąca. Itd…

Straszne 🙂

Czyli, z danych do zadania potrzebujemy na pewno termin płatności faktury i kilka komórek roboczych (można byłoby je wrzucić bezpośrednio do formuły – jak chcecie). Formatka do tego zadania wygląda tak:

Formatka

W komórkach w wierszu 3 są komórki robocze, umożliwiające parametryzację zadania:

• A3: dzień tygodnia, który nas interesuje (czwartek = 4)
• B3: numer czwartku (pierwszy, drugi, …)
• C3: numer kolejnego czwartku (pierwszy, drugi, …)
• D3: numer czwartku kolejnego miesiąca (pierwszy, drugi, …)

Zauważcie też, że daty interesujących nas czwartków są w kolejności odwrotnej (nie chronologicznie). Jest to istotne dla formuły wyliczającej datę płatności – ta kolejność musi taka być.

Logika…

A jeśli chodzi o logikę, to jest ona prosta: mamy do sprawdzenia 3 daty:

1. Pierwszy czwartek miesiąca
2. Trzeci czwartek miesiąca
3. Pierwszy czwartek kolejnego miesiąca

Jeśli nasz termin płatności jest większy niż jedna z tych dat, ale mniejszy niż następna – mamy szukaną datę.

Przykładowo: pierwszy i trzeci czwartek listopada 2016 to odpowiednio 3.11 i 17.11. Jeśli termin płatności to 7.11.2016 – przelew zostanie zrealizowany dopiero 17.11 (na 3.11, mimo że bliżej, już się „nie załapał”). A gdy terminem jest 20.11 – płatność będzie dopiero 1.12 – jest to pierwszy czwartek następnego miesiąca.

Formuły

Zacznijmy od pierwszego czwartku miesiąca faktury (D6):

=MAX(JEŻELI(DZIEŃ.TYG(DATA(ROK($A6);MIESIĄC($A6);{1;2;3;4;5;6;7});2)=$A$3;DATA(ROK($A6);MIESIĄC($A6);{1;2;3;4;5;6;7});0))+7*(B$3-1)

Ta formuła różni się nieco od tej z poprzedniego wpisu tym, że datę czwartku sprawdzamy w oparciu o termin płatności, a nie określone przez użytkownika miesiąc i rok. Czyli dodałam jeszcze funkcje ROK i MIESIĄC i oparłam formułę na komórkach pomocniczych w wierszu 3. Reszta jest identyczna jak ostatnio.

Podobnie wygląda kwestia trzeciego czwartku miesiąca (C6):

=MAX(JEŻELI(DZIEŃ.TYG(DATA(ROK($A6);MIESIĄC($A6);{1;2;3;4;5;6;7});2)=$A$3;DATA(ROK($A6);MIESIĄC($A6);{1;2;3;4;5;6;7});0))+7*(C$3-1)

I na koniec pierwszy czwartek kolejnego miesiąca (B6):

=MAX(JEŻELI(DZIEŃ.TYG(DATA(ROK($A6);MIESIĄC($A6)+1;{1;2;3;4;5;6;7});2)=$A$3;DATA(ROK($A6);MIESIĄC($A6)+1;{1;2;3;4;5;6;7});0))+7*(D$3-1)

Ta formuła różni się tylko dodaniem +1 do miesiąca. Sprawdza ona bowiem pierwszy czwartek w miesiącu kolejnym (jeśli termin płatności będzie na grudzień – cudowna funkcja DATA poradzi sobie z tym i sprawdzi styczeń kolejnego roku).

Ok. To teraz czas na datę przelewu (E6). Tutaj cudu nie ma: szukamy daty, która jest większa lub równa naszemu terminowi płatności. Do tego cudownie nadaje się funkcja PODAJ.POZYCJĘ z ostatnim argumentem równym -1 (minus 1). To spowoduje, że nasza funkcja będzie właśnie w ten sposób szukała. Ma to jednak takie wymaganie, że nasze dane (w tym wypadku daty czwartków) są posortowane malejąco! To jest must have i bez tego nie zadziała (dlatego kolumny są ułożone nie-chronologicznie). Oto formuła:

=INDEKS(B6:D6;1;PODAJ.POZYCJĘ(A6;B6:D6;-1))

W efekcie otrzymujemy:

Wynik

I voilà!