Data na podstawie numeru PESEL w Excelu

Zamiana numeru PESEL na datę urodzenia

Excel w swoich funkcjach wbudowanych nie posiada funkcji umożliwiającej nam zamianę numeru PESEL na datę urodzenia. Funkcję tą możemy zbudować samodzielnie wykorzystując tzw. funkcje zagnieżdżone.W naszym przykładzie interesować nas będą numery PESEL osób urodzonych po roku 1900. Jak wiemy, jest to najniższa wartość daty, którą Excel jest nam w stanie zinterpretować jako datę.

Główne Warunki, które chcemy wziąć pod uwagę:

  • Numer PESEL składa się z 11 znaków
  • Pierwsze 6 cyfr numeru określają datę urodzenia kolejno: rok, miesiąc, dzień
  • Numery PESEL osób urodzonych po roku 2000 mają liczbę określającą miesiąc urodzenia zwiększoną o 20

Jeśli zbudowanie takiej funkcji wydaje Ci się z początku zbyt skomplikowane, najlepszym rozwiązaniem jest rozbicie naszej funkcji na  części pierwsze obliczając:

  • za pomocą funkcji FRAGMENT.TEKSTU() i WARTOŚĆ() sprawdzamy, czy liczba reprezentująca miesiąc jest większa od 12. Jeśli tak, za pomocą funkcji JEŻELI() przypisujemy początek początek roku, a więc 1900 lub 2000. Funkcji WARTOŚĆ() używamy, by zamienić tekst na liczbę.
  • dzień, miesiąc i rok przy pomocy funkcji LEWY() oraz FRAGMENT.TEKSTU(). Gdy to już zrobimy powinniśmy na miesiąc urodzenia nałożyć formułę MOD() obliczającą resztę z dzielenia przez 20. W ten sposób uzyskaliśmy miesiąc.
  • Uzyskany w powyższy sposób dzień, miesiąc i rok łączymy ze sobą za pomocą funkcji DATA().

Jeśli nasze numery PESEL przechowujemy w kolumnie A, to ostateczna postać funkcji wygląda następująco:

=DATA(JEŻELI(WARTOŚĆ(FRAGMENT.TEKSTU(A1;3;2))>12;20;19)&LEWY(A1;2);MOD(FRAGMENT.TEKSTU(A1;3;2);20);FRAGMENT.TEKSTU(A1;5;2))
Excel - Data urodzenia z numeru PESEL
Excel – Data urodzenia z numeru PESEL

Na sam koniec funkcję możemy także uzupełnić o sprawdzenie poprawności numeru – np czy zawiera 11 znaków oraz, czy wiersz zawiera jakiekolwiek wartości. Plik Excela z naszym zadaniem zamieszam poniżej:

Excel_zamiana_pesel_na_date

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Komentarz do “Data na podstawie numeru PESEL w Excelu”