Czyli formatowanie warunkowe z tygodniem zaczynającym się od poniedziałku

Załóżmy, że mamy spis faktur z ich terminami płatności:

Formatka

Ponieważ płacimy faktury zawsze na czas (oby takich było jak najwięcej! :)), to chcielibyśmy wiedzieć, które z nich należy zapłacić w przyszłym tygodniu. Dobrze by było więc, aby przyszłotygodniowe faktury zostały jakoś wyróżnione na naszej liście. Wyróżnimy je oczywiście za pomocą formatowania warunkowego.

Na pierwszy rzut oka zadanie wydaje się prościutkie, ponieważ jeśli nasze terminy płatności są prawidłowymi datami (a są, no bo przecież jesteśmy świadomymi użytkownikami Excela :)), to formatowanie warunkowe zawiera wbudowaną funkcjonalność wyróżniania dat z przyszłego tygodnia. Ale, dla nas Polaków – ta funkcjonalność ma pewien minus, który może bardzo denerwować niektórych z nas  i jednocześnie uniemożliwiać korzystanie z tej funkcjonalności… Excel bowiem jest Amerykaninem, czyli zaczyna tydzień od niedzieli, a my, w Polsce, chcemy od poniedziałku.

Spójrzmy jak wygląda nasza formatka, jeśli zaznaczymy na niej daty z przyszłego tygodnia, korzystając z wbudowanej opcji formatowania warunkowego (zaznacz terminy płatności i Narzędzia główne/ Formatowanie warunkowe/  reguły wyróżniania komórek/ Data występująca/ W przyszłym tygodniu):

Wynik wbudowanej funkcjonalności formatowania warunkowego

Zakładając, że dzisiejsza data to 4 sierpnia 2018, przyszły tydzień to zakres dat od 6. do 12. sierpnia (patrz obrazek poniżej). Natomiast, co widać na powyższym obrazku, Excel zaznaczył też 5 sierpnia (niedziela obecnego tygodnia) i nie zaznaczył 12 sierpnia (niedziela przyszłego tygodnia). Stalo się tak dlatego,że on zaczyna tydzień od niedzieli i, niestety, nie mają na to wpływu ustawienia systemu operacyjnego (u mnie na komputerze tydzień zaczyna się od poniedziałku).

Kalendarz Sierpień 2018

Z tym właśnie problemem będziemy się mierzyć.

A tak swoją drogą: czy to jest faktycznie jakiś straszny problem? Dla mnie nie :). W rozpatrywanym przypadku – zapłacę fakturę trochę wcześniej, tragedii nie będzie. Ja lubię rozwiązania proste, więc skorzystałabym z wbudowanej funkcjonalności formatowania warunkowego. Problem pojawiłby się, gdybyśmy musieli pisać formułę w innej kolumnie – tutaj już trzeba pisać formułę…

Logika problemu

Teoretycznie sprawa jest prosta. Jeśli obecny tydzień ma numer np. 31, to kolejny będzie miał o jeden więcej, czyli 32. W Excelu są nawet 2 funkcje, które umieją określić numer tygodnia w roku: NUM.TYG i ISO.NUM.TYG. W zależności od sposobu numerowania tygodni – zwracają odpowiedni numer tygodnia. Moglibyśmy użyć którejkolwiek z nich, z odpowiednimi ustawieniami i by zadziałało. Natomiast problem pojawia się, gdy mamy przekroczenie roku. Wtedy ostatni tydzień to 52 lub 53, a kolejny to 1, a nie o jeden większy od poprzedniego… Przyznam, że gdy myślę o formule, która musiałby uwzględnić wszystkie możliwe tutaj sytuacje, to mnie głowa boli! Jeżeli to, to tamto, a jeżeli to lub to i tamto, to siamto. Masakra. Zbitek funkcji logicznych i pewnie i tak coś byśmy pominęli.

Krótko mówiąc: nie ma co się pchać w takie rozwiązanie (ech, gdyby nie ten przełom roku…;)). Na pewno istnieje jakieś prostsze” przecież tydzień to kolejne 7 dni, w naszym przypadku od poniedziałku do piątku, niezależnie od tego, jaki to jest miesiąc czy rok! Musi być łatwiejszy sposób!

I jest oczywiście. Nie będę się w nim odnosiła do numerów tygodni, tylko do samych dat i tego, którym dniem tygodnia są.

Skoro dziś jest np. 4 sierpnia, to za tydzień, czyli za 7 dni, będzie 11 sierpnia (4+7=11) i ta data na pewno będzie w przyszłym tygodniu. To oczywiste (i taki case już opisywałam). Problem jest taki, że mamy odwtorną sytuację, tzn. mamy datę hipotetycznie z przyszłego tygodnia, mamy też datę dzisiejszą i na tej podstawie ustalamy, czy owa pierwsza data jest w przyszłym tygodniu. A niekoniecznie różnica między tymi datami wynosi 7. Np. dziś jest 4 sierpnia, a szukaną datą jest 7 sierpnia. Różnica między nimi to 3 dni. Natomiast ponieważ 4 sierpnia to sobota, a 7 to wtorek, to tak – 7 sierpnia jest w przyszłym tygodniu.

I znowu, żeby sprawdzać, czy szukana data mieści się w jakimś przedziale i do tego spełnia określone warunki dnia tygodnia – wyszłaby nam formuła-tasiemiec. Brrr, tego nie chcemy. Musimy więc znaleźć jakieś uniwersalne rozwiązanie, które uwzględni siedmiodniową różnicę między datami.

Takim rozwiązaniem jest „przerobienie” obu dat na takie, między którymi jest różnica 7, jeśli są one oczywiście z następujących po sobie tygodni. Tylko jak dokonać takiego przerobienia skoro są różne różnice w zależności od dnia tygodni? Wykorzystać ten dzień tygodnia i „pozbyć się” go. Z każdą datą „wrócimy” niejako do początku tygodnia odejmując numer dnia tygodnia od daty, reprezentującej ten dzień. Dokonamy standaryzacji sobie obu dat.

Czyli np. dziś jest 4 sierpnia i jest to sobota. Sobota to 6. dzień tygodnia, więc od liczby, reprezentującej dzisiejszą datę, czyli 43316 odejmuję 6, co mi daje 43310, czyli dzień 29 lipca (niedziela poprzedniego tygodnia). Mogę do tego dodać 1, aby uzyskać poniedziałek, ale matematycznie nie będzie miało to znaczenia, ponieważ dokładnie taką samą operację zrobimy z drugą datą, czyli 7 sierpnia (wtorek, czyli odejmujemy 2), uzyskując 5 sierpnia (również niedziela poprzedniego tygodnia). Między tymi nowymi datami mamy dokładnie 7 dni różnicy. A taka różnica właśnie definiuje nam „przyszły tydzień”.

Tę nieco pokrętną logikę prezentuje poniższy obrazek:

Logika

Jak już rozumiemy zagadnienie, to formuła będzie już pikusiem 🙂

Formuła

Najpierw napiszę funkcje JEŻELI w żółtych komórkach formatki (E6:E16), a następnie pierwszy argument tej funkcji skopiuję do formatowania warunkowego i dostanę ładne wyróżnienie kolorem. A formuła jest taka:

=JEŻELI($E$3-DZIEŃ.TYG($E$3;2)=$B6-DZIEŃ.TYG($B6;2)-7;"przyszły tydzień";"")

Funkcja DZIEŃ.TYG określa numer dnia tygodnia, ze swoim drugim argumentem równym 2 – zaczyna numerację dni tygodnia od poniedziałku, czyli coś, co nas interesuje. Czyli od liczby reprezentującej dzisiejszy dzień odejmujemy jej numer dnia tygodnia, a następnie sprawdzamy, czy nowa liczba jest równa 7 plus liczba uzyskana w wyniku takiej samej operacji na dniu szukanym. Jeśli tak – mamy przyszły tydzień, jeśli nie – inny tydzień.

Efekt formuły wygląda tak (dla porównania zostawiłam wynik wbudowanej funkcjonalności formatowania warunkowego):

Wynik formuły

Teraz zostaje już tylko wrzucenie tej formuły do formatowania warunkowego.

Formatowanie warunkowe – formuła

Konkretnie będziemy wrzucać do niego tę część formuły:

=$E$3-DZIEŃ.TYG($E$3;2)=$B6-DZIEŃ.TYG($B6;2)-7

Aby to zrobić:

1. Zaznacz daty, które chcesz formatować warunkowo, czyli B6:B16

2. Wybierz: Narzędzia główne/ Formatowanie warunkowe/ Nowa reguła/ Użyj formuły do określenia komórek, które chcesz formatować

3. Wpisz wspomnianą wcześniej formułę i wybierz format komórki:

Ustawienia formatowania warunkowego

BTW formuła jest tak przygotowana, że po rozszerzeniu zakresu formatowania warunkowego na całą tabelę – pokoloruje całe wiersze:

I tyle! Efekt wszystkiego jest taki:

Wynik

A co z przełomem roku?

Hehe, też zadziała :). Rozwiązanie jest niezależne od roku czy miesiąca. Działa po prostu na datach, czyli liczbach. Zobaczmy jak się zachowa, gdy jako dzisiejszą datę ustawimy 2018-12-27. Kalendarz na grudzień wygląda tak:

Kalendarz Grudzień 2018

A nasze rozwiązanie tak:

Test na przełom roku

Wygląda na to, że działa :).

I wersja wideo: