Jak zapewne wiesz, numer PESEL posiada zakodowaną datę urodzenia. Pierwsze dwie cyfry numeru PESEL to rok urodzenia, dwie kolejne to miesiąc a następne dwie to dzień urodzenia. Sytuacja się nieco komplikuje w przypadku osób urodzonych po roku 1999. Otóż wtedy do numeru miesiąca jest dodawana liczba 20. I tak: osoba urodzona 25 listopada roku 1903 będzie miała PESEL zaczynający się od cyfr 031125. Natomiast osoba urodzona 25 listopada roku 2003 będzie miała PESEL zaczynający się od cyfr 033125.
Tutaj jeszcze mała uwaga: jeśli wpiszesz w Excelu numer PESEL zaczynający się od zera, to początkowe zero zostanie „ucięte”. Aby zapobiec tej sytuacji należy numer PESEL poprzedzić apostrofem (lub zmienić formatowanie komórek na tekstowe). Dokładniej to zagadnienie opisałem we wpisie: Jak w Excelu wpisać PESEL zaczynający się od zera?
Aby na podstawie numeru PESEL odczytać rok urodzenia należy za pomocą funkcji LEWY wyciągnąć z numeru PESEL dwie pierwsze cyfry. Załóżmy, że numer PESEL znajduje się w komórce A2. Wtedy nasza funkcja wygląda następująco:
=LEWY(A2; 2)Ponieważ rok jest zapisany jako dwie ostatnie cyfry, to należy z przodu dodać np. 19 (na razie nie rozważamy przypadku, że PESEL może należeć do osoby urodzonej po roku 1999). W tym celu nasza formuła wygląda następująco:
="19"&LEWY(A2;2)Znak & służy do łączenia ciągów znaków. Chcemy aby formuła połączyła nam ciąg znaków 19 i dwie ostatnie cyfry roku urodzenia. Inny sposób to do wyciągniętego roku (2 cyfry) dodanie liczby 1900 czyli mamy taką formułę:
=LEWY(A2;2)+1900Aby wydobyć miesiąc urodzenia należy użyć funkcji FRAGMENT.TEKSTU, która przyjmuje trzy argumenty. Pierwszy argument to tekst z którego wyciągamy pewien fragment (numer PESEL). Drugi argument mówi, od którego znaku wyciągamy ten fragment tekstu a trzeci argument nam mówi ile znaków będziemy pobierali. Miesiąc urodzenia zaczyna się od trzeciego znaku numeru PESEL i zajmuje dokładnie dwa znaki. Więc nasza funkcja wygląda następująco:
=FRAGMENT.TEKSTU(A2; 3; 2)Analogicznie wyciągamy dzień urodzenia, z tym że pobieramy go od piątego znaku. Więc nasza funkcja wygląda następująco:
=FRAGMENT.TEKSTU(A2; 5; 2)Sytuacja się komplikuje, gdy nie mamy pewności czy numer PESEL nie należy do osoby, która urodziła się po roku 1999. Aby wydobyć rok urodzenia należy sprawdzić, czy miesiąc jest większy od 20. Jeśli jest większy od 20 to znaczy, że wydobyty rok należy poprzedzić ciągiem znaków 20 (lub do roku dodać liczbę 2000) w przeciwnym przypadku poprzedzamy ciągiem znaków 19 (lub dodajemy liczbę 1900). Należy tutaj skorzystać z funkcji JEŻELI. Oto przykładowa formuła, która nam wyciąga z numeru PESEL rok urodzenia:
=JEŻELI(WARTOŚĆ(FRAGMENT.TEKSTU(A2; 3; 2))>20; "20"&LEWY(A2; 2); "19"&LEWY(A2; 2)) Dodatkowo w warunku w funkcji JEŻELI musiałem użyć funkcji WARTOŚĆ, która ciąg tekstowy wydobyty przez funkcję FRAGMENT.TEKSTU zamienia na liczbę (inaczej porównanie przy funkcji JEŻELI nie będzie działać prawidłowo). O co chodzi z funkcją WARTOŚĆ dokładniej opisałem w notce: Problem z porównywaniem liczb wyciąganych z kodu (np. PESELa) za pomocą funkcji tekstowych.
Aby wydobyć miesiąc możemy uzyć funkcji MOD, która zwraca resztę z dzielenia. Weźmiemy pod uwagę resztę z dzielenia przez 20. Załóżmy, że miesiąc to 3 (marzec). Dzieląc go na 20 wychodzi wynik 0 i 3 reszty. Jeśli miesiąc to 23 (czyli osoba urodziła się również w marcu, ale po roku 1999) to wtedy dzielenie nam zwróci wynik 1 i reszta 3. Oto funkcja, która wydobywa miesiąc urodzenia:
=MOD(FRAGMENT.TEKSTU(A2; 3; 2); 20)Wydobycie dnia urodzenia jest prostsze. Tutaj już nie musimy sprawdzać dodatkowych warunków. Wystarczy użyć samej funkcji FRAGMENT.TEKSTU. I nasza formuła wygląda bardzo prosto
=FRAGMENT.TEKSTU(A2; 5; 2)Teraz pozostaje z trzech liczb (rok, miesiąc i dzień urodzenia) złożyć datę w formacie Excelowym (jest to przydatne gdy np. będziemy chcieli obliczyć ile dana osoba ma lat – tutaj można skorzystać z nieudokumentowanej funkcji DATA.RÓŻNICA którą opisałem w notce Ile dni, miesięcy, lat minęło pomiędzy dwiema datami?). Do tego celu służy funkcja o nazwie DATA, która przyjmuje trzy argumenty: rok, miesiąc i dzień. W naszym przypadku będzie to
=DATA(B2; C2; D2)Gotowy arkusz z wklepanymi funkcjami możesz pobrać tutaj.
Ciekawostka: w numerze PESEL zakodowana jest również płeć. Więcej informacji znajdziesz w notce: Jak z numeru PESEL odczytać płeć
