fbpx
Minuty czytania: 4

Data urodzenia z PESEL – krok po kroku

Szczęśliwi czasu nie liczą…ręcznie. Czytając dalej dowiesz się jak wyciągnąć z numeru PESEL datę urodzenia. Następnie na jej podstawie z łatwością policzysz wiek. O tym jak często trzeba taką umiejętność wykorzystywać, wie każdy HR-owiec. Ale nie tylko im przydadzą się opisane tutaj umiejętności.

data urodzenia z pesel

 

Parę słów na temat numeru PESEL

Numer PESEL ma zakodowanych kilka informacji. Między innymi wiek – pierwsze 6 cyfr i płeć – 10 cyfra numeru. Jeżeli chodzi o wiek, zasada do roku 2000 była prosta:

  • pierwsze dwie cyfry oznaczają rok (a konkretniej, dwie ostatnie cyfry roku),
  • kolejne dwie – miesiąc 01-12,
  • i na ostatnie – dzień 01-31

Od roku 2000 sprawa się skomplikowała. Dlatego dla odróżnienia stuleci wprowadzono zasadę, że do miesiąca urodzenia będzie dodawana określona liczba w zależności od roku w jakim ktoś się urodził. I tak dla osób urodzonych w latach:

  • 2000–2099 – dodajemy 20,
  • 2100–2199 – dodamy 40,
  • a dla 2200–2299 – dodamy 60.

Dla porządku – dla osób urodzonych w latach 1800–1899 dodaje się 80. Ale chyba nikt w podobnym wieku tego nie przeczyta 😉

Co to znaczy w praktyce? Jeżeli ktoś urodził się 9 czerwca 2011 roku, to pierwsze 6 cyfr jego numeru PESEL będzie wyglądało tak: 112609.

  • 11 – bo rok 2011 (ostatnie dwie cyfry)
  • 26 – bo do liczby miesiąca urodzenia 6, dodajemy 20 (dla osób urodzonych w latach 2000-2099)
  • 09 – bo 9 dzień urodzenia.

Co będzie Ci potrzebne

Aby policzyć wiek osoby na podstawie numeru PESEL, najpierw musisz „wyciągnąć” datę urodzenia. Następnie, na podstawie tej daty i np. daty aktualnej z łatwością policzysz wiek. Będziesz musiał połączyć w jedną formułę kilka funkcji:

  1. JEŻELI – będzie Ci potrzebne do sterowania jakie funkcje mają być „uruchomione” (patrz punkt 6)
  2. WARTOŚĆ – do zamiany tekstu na datę. Szczególnie, że PESEL powinien być wpisany w komórkach jako tekst (inaczej nie zapiszesz np. początkowego zera w wybranych przypadkach)
  3. FRAGMENT.TEKSTU – bo musisz wydobyć z numeru PESEL określone fragmenty.
  4. LEWY – do wyciągnięcia początkowego fragmentu (alternatywnie możesz cały czas stosować FRAGMENT.TEKSTU)
  5. DATA – do zamiany fragmentów tekstu na datę
  6. MOD – dzięki tej funkcji możesz zwrócić resztę z dzielenia. Wykorzystamy to do sprawdzenia czy masz do czynienia z PESEL-em osoby urodzonej przed lub po 1999 roku.

Data urodzenia z PESEL – do dzieła!

Zawsze, kiedy ostatecznie Twoja formuła osiąga finalnie taki wygląd:

=JEŻELI(WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>12;DATA(„20″&LEWY(B8;2);MOD(FRAGMENT.TEKSTU(B8;3;2);20);FRAGMENT.TEKSTU(B8;5;2));DATA(„19″&LEWY(B8;2);FRAGMENT.TEKSTU(B8;3;2);FRAGMENT.TEKSTU(B8;5;2)))

musisz zacząć od pierwszego kroku. Zawsze! 😉

Stąd możesz pobrać plik początkowy, aby krok po kroku zrealizować ze mną zadanie. Na początek przyjmijmy założenie, że będziemy mieć do czynienia z PESEL-ami osób urodzonych w XX i XXI wieku (lata 19xx i 20xx) W komórce B8 pliku startowego mamy PESEL 84053112670. Do dzieła:

  1. Datę możemy zbudować za pomocą funkcji DATA(rok; miesiąc; dzień). Dlatego z numeru PESEL musisz wyciągnąć…rok, miesiąc, dzień 😉
  2. Rok to pierwsze dwie cyfry w PESEL-u. Użyjemy funkcji =LEWY(B8;2), w wyniku czego otrzymamy 84.
  3. Miesiąc to cyfry 3 i 4. Użyjmy funkcji =FRAGMENT.TEKSTU(B8;3;2) i mamy 05.
  4. Dzień to cyfry 5 i 6. Zatem =FRAGMENT.TEKSTU(B8;5;2) da nam 31.
  5. Mamy już rok, miesiąc i dzień. Połączmy teraz wszystko w funkcji DATA:
    =DATA(„19″&LEWY(B8;2); FRAGMENT.TEKSTU(B8;3;2); FRAGMENT.TEKSTU(B8;5;2))
    Jak widzisz w pierwszym argumencie funkcji DATA połączyliśmy (&) tekst „19” z funkcją LEWY(B8;2). Robimy tak dlatego, że funkcja DATA w argumencie rok wymaga roku w formacie RRRR.

W wyniku otrzymujemy datę 1984-05-31 (pamiętaj, że komórka musi mieć format daty) Koniec? Nieeee.

A co z osobami urodzonymi po 1999 roku?

Wszystko pięknie, jeżeli będziesz tym sposobem wyciągać datę z PESEL-u osoby urodzonej do roku 1999 włącznie. Ale to nie wystarczy do obliczenia daty osób urodzonych po 1999. Jak pamiętasz, dla tych osób do liczby oznaczającej miesiąc dodawana jest liczba 20.

Dlatego, musimy znaleźć sposób na rozróżnienie osób urodzonych przed i po 1999 roku, aby w każdym przypadku zastosować nieco inną formułę. Jeżeli liczba oznaczająca miesiąc będzie większa niż 12, to mamy do czynienia z osobą urodzoną po 1999 roku (do liczby oznaczającej miesiąc dodano 20) Kolejne kroki to:

  1. Sprawdzamy, czy PESEL dotyczy osoby urodzonej po 1999 roku: =JEŻELI(WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>12;… – wprowadziliśmy funkcję WARTOŚĆ, żeby zamienić tekst na wartość i móc ją porównać z 12.
  2. Jeżeli wartość jest większa niż 12 (osoba urodzona po 1999 roku) to zastosujemy taką formułę: DATA(„20″&LEWY(B8;2);MOD(FRAGMENT.TEKSTU(B8;3;2);20);
  3. Skorzystaliśmy z funkcji MOD, która zwraca resztę z dzielenia. Dzięki temu możemy wyciągnąć faktyczną liczbę oznaczającą miesiąc urodzenia.
  4. Dla osób urodzonych przed i w roku 1999 zastosujemy wcześniej opisany sposób: DATA(„19″&LEWY(B8;2);FRAGMENT.TEKSTU(B8;3;2);FRAGMENT.TEKSTU(B8;5;2)

Całość będzie miała postać jak niżej:

=JEŻELI(WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>12;DATA(„20″&LEWY(B8;2);MOD(FRAGMENT.TEKSTU(B8;3;2);20);FRAGMENT.TEKSTU(B8;5;2));DATA(„19″&LEWY(B8;2);FRAGMENT.TEKSTU(B8;3;2);FRAGMENT.TEKSTU(B8;5;2)))

Taka formuła pozwoli wyciągnąć datę urodzenia, niezależnie od tego czy liczymy ją z PESEL-u osoby urodzonej przed, czy po 1999 roku. Końcowy efekt możesz także sprawdzić w pliku.

Oblicz wiek

Wiek w latach obliczysz korzystając z funkcji DATA.RÓŻNICA. Datę urodzenia mamy w komórce C8. Policzymy różnicę w latach w stosunku do aktualnej daty – funkcja DZIŚ(). Zapiszemy:

=DATA.RÓŻNICA(C8;DZIŚ();”Y”)
Parametr „Y” daje różnicę w latach, „M” w miesiącach, „D” w dniach. Gotowe. Mamy wiek z PESEL-u! Oczywiście możesz połączyć wszystkie funkcje w jedną jeszcze bardziej skomplikowaną formułę, która da dokładnie ten sam efekt:
=DATA.RÓŻNICA(JEŻELI(WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>12;DATA(„20″&LEWY(B8;2);MOD(FRAGMENT.TEKSTU(B8;3;2);20);FRAGMENT.TEKSTU(B8;5;2));DATA(„19″&LEWY(B8;2);FRAGMENT.TEKSTU(B8;3;2);FRAGMENT.TEKSTU(B8;5;2)));DZIŚ();”Y”)
 

Chcesz darmowy szablon Excel do prowadzenia projektów i śledzenia zadań?


Podsumowanie

Jak widzisz im więcej warunków, tym bardziej złożona formuła. Ale zawsze jeżeli rozbijesz zadanie na części pierwsze – całość stanie się dużo łatwiejsza. Na szkoleniach powtarzam, że każda finalnie złożona reguła zaczyna się od środka. Znajdując rozwiązanie każdego problemu po kolei – ostatecznie dochodzisz do finalnego rozwiązania.

Całe zadanie zamiany numeru PESEL w wiek (oraz płeć) można też zrealizować funkcją w VBA. Może wspólnie zmierzymy się z tematem w jednym z kolejnych artykułów? Daj proszę znać czy byłoby to dla Ciebie interesujące.


Masz inne problemy z Excelem? Zapraszam Cię na grupę – Keep Calm And Use Excel na Facebooku. To miejsce, w którym pomagamy sobie nawzajem w codziennych wyzwaniach z Excelem 😉



2 Komentarze

Tybot · 4 grudnia 2019 o 17:39

Hmm..
Wstawiłem opisaną formułę do jednej komórki (mianowicie C8) i wyniki wyszły wszystkie o jeden rok więcej niż w przykładzie z osobnym liczeniem wieku w kolejnej komórce. Dlaczego?

    Łukasz Pietrzak · 6 grudnia 2019 o 19:05

    W komórce C8 powinna być data, a nie wiek. Jeżeli możesz wyślij swój plik na kontakt@excelness.com – postaram się pomóc.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *