Ocena użytkowników: 3 / 5

Gwiazdka aktywnaGwiazdka aktywnaGwiazdka aktywnaGwiazdka nieaktywnaGwiazdka nieaktywna
 

Poszukiwałem w necie informacji jak wyciągnąć datę urodzenia z numeru PESEL. Niestety wszystkie rozwiązania Excel-mistrzów sprowadzają się do daty urodzenia z ubiegłego wieku. Wszystkie znalezione rozwiązania omijały problem osób urodzonych po 2000 roku. Postanowiłem podjąć wyzwanie i przygotować formułę, która będzie poprawnie interpretowała numer PESEL dla osób urodzonych od 1900 do 2299 roku.

Wszystkim wiadomo, że w pierwszych sześciu cyfrach numeru PESEL zawarta jest data urodzenia, podana w kolejności RRMMDDxxxxx.
O ile daty urodzin w latach 1900-1999 nie przedstawiają problemu, o tyle zdziwienie może wywołać sytuacja w której miesiąc jest oznaczony np. liczbą 89.
Do odpowiedniego miesiąca dodano odpowiednie wartości w celu obsłużenia dat urodzenia z innych wieków niż XX.

Jeśli do miesiąca dodano liczbę 80 (jak we wspomnianym przypadku) to data urodzenia dotyczy lat 1800-1899. W latach 2000-2099 dodajemy do miesiąca liczbę 20, w latach 2100-2199 nalezy do miesiąca dodać liczbę 40 i odpowiednio dla daty urodzenia pomiędzy 2200-2299 trzeba dodać do miesiąca liczbę 60.

Formuła obliczająca datę urodzenia to (zakładam, że numer PESEL jest w komórce A1):

=DATA(1900+FRAGMENT.TEKSTU(A1;1;2)+CZ.CAŁK.DZIELENIA(FRAGMENT.TEKSTU(A1;3;2);20)*100;MOD(FRAGMENT.TEKSTU(A1;3;2);20);FRAGMENT.TEKSTU(A1;5;2))  (*)

* Po głębszym przeanalizowaniu funkcji okazało się, że nie działa ona poprawnie w wypadku gdy na początku PESELu jest 0. Poprawna implementacja funkcji na końcu artykułu

Formuła działa poprawnie dla lat 1900-2299. Przypadek urodzenia w latach 1800-1899 pominąłem, jako aspekt problematyczny, ponieważ daty poniżej 1900 roku są przez arkusz inaczej traktowane. Aby zobaczyć, że tak jest wystarczy wpisać formułę:

=DATA(1899;11;30)

która spowoduje wyświetlenie wartości: 30.11.3799. Można więc przyjąć, że 1.01.1900 jest dla Excela "początkiem Świata". smiley

2017.12.26 - poprawki

Okazało się, że powyższa funkcja robiła błędy gdy w peselu pierwszą cyfrą było zero. Dlatego musiałem ją "nieco" skorygować. Oto poprawna formuła:

=JEŻELI(DŁ(A1)=11;DATA(1900+FRAGMENT.TEKSTU(A1;1;2)+CZ.CAŁK.DZIELENIA(FRAGMENT.TEKSTU(A1;3;2);20)*100;MOD(FRAGMENT.TEKSTU(A1;3;2);20);FRAGMENT.TEKSTU(A1;5;2));JEŻELI(DŁ(A1)=10;DATA(1900+FRAGMENT.TEKSTU(A1;1;1)+CZ.CAŁK.DZIELENIA(FRAGMENT.TEKSTU(A1;2;2);20)*100;MOD(FRAGMENT.TEKSTU(A1;2;2);20);FRAGMENT.TEKSTU(A1;4;2));JEŻELI(DŁ(A1)=9;DATA(1900+CZ.CAŁK.DZIELENIA(FRAGMENT.TEKSTU(A1;1;2);20)*100;MOD(FRAGMENT.TEKSTU(A1;1;2);20);FRAGMENT.TEKSTU(A1;3;2));"Błąd")))

Prawa autorskie: Jestem autorem powyższej formuły, możesz więc wykorzystywać tę formułę w dowolny sposób. Gdybyś miał jednak ją gdzieś publikować, byłoby niezmiernie miło gdybyś nie zapomniał(a) napisać kto jest jej autorem i napisać z jakiej strony pochodzi pomysł. Z góry dziękuję za pozytywne potraktowanie mojej prośby.

Pozdrawiam, Bogdan Miloch