Czyli dwie ciekawe funkcje daty

Załóżmy, że mamy określić daty, kiedy nasi pracownicy mogą przejść na emeryturę. Jedynym naszym kryterium w tym wypadku jest wiek i płeć pracownika. Czyli kobieta może przejść na emeryturę w wieku 60 lat, mężczyzna natomiast – w wieku 65 lat.

Dostajemy z systemu następujące informacje:

• imię i nazwisko pracownika
• płeć,
• rok, miesiąc i dzień urodzenia.

Na tej podstawie mamy określić kiedy pracownik może przejść na emeryturę. Chcemy dostać coś takiego:

Do tego obliczymy sobie jeszcze ile pełnych lat pracy zostało każdemu pracownikowi oraz zaznaczymy kolorem tych, którzy już osiągnęli wiek emerytalny.

Formatka

Z systemu dostajemy dane na takiej formatce:

Formatka

Z zakresu, który otrzymałam z systemu stworzyłam obiekt tabela o nazwie tbPracownicy. Dodatkowo w kolumnie C mam żółte komórki, w których określam w jakim wieku są kobiety i mężczyźni, kiedy mogą przejść na emeryturę. Określam też dzisiejszą datę (można z powodzeniem użyć funkcji DZIŚ).

Aby obliczyć datę przejścia na emeryturę w Excelu, najpierw potrzebujemy określić datę urodzenia pracownika w jednej komórce. W zależności od płci pracownika – będzie mógł on przejść na emeryturę albo w wieku 60 lat (kobieta), albo 65 (mężczyzna).

Określanie daty urodzenia pracownika

W kolumnie H mojego arkusza utworzę kolumnę Data ur., w której określę datę urodzenia pracownika. Użyję w tym celu funkcji DATA, która potrafi stworzyć datę na podstawie roku, miesiąca i dnia podanych w innych komórkach. Funkcja ta radzi sobie nawet z tekstami.

Formuła w komórce H8 wygląda tak:

=DATA([@[Rok ur.]];[@[Mc ur.]];[@[Dzień ur.]])

A jeśli nie korzystasz z tabel (i odwołań strukturalnych w nich), wpisz taką formułę:

=DATA(E8;F8;G8)

Powstaje nam następująca kolumna:

Określanie daty urodzenia

Określanie daty przejścia na emeryturę

Teraz, na podstawie płci pracownika określimy datę za 60 lub 65 lat, zaczynając od daty urodzenia pracownika. Płeć mamy, więc najprościej za pomocą funkcji JEŻELI ustalimy liczbę lat, a za pomocą funkcji NR.SER.DATY ustalimy konkretną datę.

Zacznę formułę od NR.SER.DATY, gdyż to datę docelowo mam ustalić. Zawsze moim punktem startowym będzie data urodzenia pracownika. Punkt końcowy muszę tej funkcji podać w miesiącach, zatem pomnożę 12 razy 60 lub 65 – w zależności od płci.

Formuła w kolejnej kolumnie – Data emerytury – wygląda tak:

=NR.SER.DATY([@[Data ur.]];12*JEŻELI([@Płeć]="K";$C$3;$C$4))

A tak wyniki:

Określanie daty emerytury

Wyróżnianie kolorem osób, które osiągnęły wiek emerytalny

Ale, ale! Zobacz na wiersz 17, w którym pani Ewa już powinna być na emeryturze (mamy kwiecień, a ona 60 lat skończyła w marcu). Chciałabym takie osoby wyróżnić kolorem. Użyję do tego formatowania warunkowego. W tym celu:

1. Zaznaczę kolumnę Data emerytury tabeli

2. Wejdę do Narzędzia główne/ Formatowanie warunkowe/ Nowa reguła/ Formatuj tylko komórki zawierające

3. Ustawię regułę, że wartość komórki ma być mniejsza lub równa dzisiejszej dacie. Możesz z powodzeniem użyć funkcji DZIŚ do jej określenia. Ja tę datę mam w komórce, więc wskażę tę komórkę – C5.

4. Następnie wybierz sposób wyróżnienia (przycisk Formatuj)

5. Zatwierdź OK.

Wszystkie powyższe kroki przedstawia rysunek:

Reguła wyróżniania osób w wieku emerytalnym

Wynik tej reguły wygląda następująco:

Wyróżnienie osób w wieku emerytalnym

Ile pełnych lat zostało pracownikowi do emerytury?

To teraz zostało tylko określić, ile pełnych lat zostało pracownikowi do emerytury? Do tego użyjemy funkcji-widmo: DATA.RÓŻNICA. Dlaczego widmo? Ponieważ nigdzie w Excelu jej nie znajdziesz: w menu Formuły, nie ma swojego kreatora czy nawet podpowiedzi składni! Dlaczego? Nie mam pojęcia i strasznie się temu dziwię…

Funkcja ta ma za zadanie obliczanie różnicy między dwiema datami. Wynik może wyświetlić na różne sposoby: w dniach, miesiącach, latach,… Nas interesują pełne lata, więc wpiszmy ręcznie taką formułę:

=JEŻELI([@[Data emerytury]]<=$C$5;0;DATA.RÓŻNICA($C$5;[@[Data emerytury]];"y"))

Całość wrzuciłam w funkcję JEŻELI, aby dla osób, które są już w wieku emerytalnym wyświetlić, że mają do przepracowania 0 pełnych lat. Gdybym tego nie zrobiła – wyświetliłby się przy nich błąd.

Tak wygląda wynik:

WYNIK

Przydatne? 🙂

Powiązane produkty

• Funkcje dat – excelowy niezbędnik – lekcja omawiająca najważniejsze funkcje dat, które pozwolą Ci w prosty sposób dokonywać obliczeń na datach, bez kombinowania jak koń pod górę.
• Formatowanie warunkowe – podczas tej lekcji nauczysz się korzystać z cudownego narzędzia, jakim jest formatowanie warunkowe. Będziesz umiał(a) korzystać zarówno z wbudowanych opcji, np. dotyczących dat, jak również napiszesz swoje własne reguły (np. zaznaczanie całych wierszy z danymi)
• O obiekcie tabela słów kilka – podczas tej lekcji pokazuję czym jest obiekt tabela w Excelu i dlaczego warto z niego korzystać. Daje nam on ogromne możliwości dynamizowania naszych arkuszy, a wiele nowych funkcjonalności Excela wręcz wymaga przechowywanie danych w tymże obiekcie (np. Power Query). Must have każdego użytkownika Excela!

Plik do pobrania:

MalinowyExcel Kiedy pracownik może pójść na emeryturę dw.xlsx

I film na Youtubie, gdzie pokazuję powyższe rozwiązanie: