Rodné číslo je unikátní identifikátor přiřazený každému občanovi České republiky. Velice často se tak s tímto údajem setkáte při analýze dat českých společnosti. Pro výpočet data narození z rodného čísla budeme používat hlavně textové funkce a pro rozlišení některých speciálních případů budeme potřebovat podmínku s pomocí funkce KDYŽ().

Pokud ve vašem zaměstnání zpracováváte data klientů, nebo Vás právě čeká zkouška z excelu na vysoké škole, můžete narazit na velice častý problém. Jak z rodného čísla dostat datum narození. Problém, na který dříve či později narazí každý z nás. Veškerá ročná čísla použitá v tomto článku jsou vygenerována náhodně. 

Rodné číslo a jeho struktura

Ročné číslo se skládá z deseti číslic. Prvních šest určuje datum narození vlastníka rodného čísla, kde první dvojčíslí je rok narození. Následuje měsíc narození, kde dochází i k rozlišení můžu a žen. Každá žena bude mít k rodnému číslu přičtenou hodnotu 50. Podívejme se tedy na smyšlené rodné číslo na kterém si ukážeme co čísla na které pozici obsahují

960418/0190 

• 96 – Rok narození 
• 04 – Měsíc narození 
• 18 – Den narození 
• 019 – Pořadí 
• 0 – Kontrolní číslice 

Pro další informace o rodných číslech přidávám odkaz na článek o rodných číslech na české wikipedii, který detailněji popisuje historii rodného čísla v ČR a také zmiňuje různé varianty používané historicky.

Jdeme na to 

Jak jsme si už řekli, rodné číslo se skládá ze čtyřech částí. Nás zajímají ty první tři, ze kterých získáme datum narození. Pro názornost si celý vzorec nejprve rozepíšeme na jednotlivé části a na konci jej opět složíme do jednoho dlouhého vzorce. Předem ale podotýkám, že výsledná funkce bude pro nováčky v excelu opravdu docela složitá. 

Rodné číslo – rok narození 

Rok narození bude první částí rodného čísla, kterou budeme potřebovat, a hned zde narazíme na problém. Na internetu koluje spousta variant tohoto vzorce, a u některých starších příkladů se zcela zapomíná na to, že existuje rok 2000 a lidé se mohou rodit i po tomto roce. 

=ZLEVA(F6;2)*1+1900 

Tento vzorec vezme první dva znaky rodného čísla (funkce ZLEVA() může být v tomto případě nahrazena i funkcí ČÁST()). V případě že má ale někdo datum narození v roce 2000. Bude se v rodném čísle zobrazovat pouze “00”, což v tomto vzorci dá datum narození 1900. Výsledkem tak bude rozdíl cca 100 let, což je docela zásadní rozdíl. Musíme proto ošetřít, aby v případech, kdy se na prvních dvou znacích rodného čísla objěvují hodnoty 00–20, došlo k úpravě roku narození na rok 2000. Proto přidáme do vzorce podmínku, která bude kontrolovat zda první dva znaky jsou menší než 20 (aktuální rok) a na základě výsledku vrátí hodnotu ZLEVA(F6;2)*1+1900 nebo ZLEVA(F6;2)+2000. 

=KDYŽ(ZLEVA(F6;2)*1<=20;ZLEVA(F6;2)+2000;ZLEVA(F6;2)+1900) 

Aby bylo možné vzorec používat i do buducnosti bez toho, aby bylo nutné vždy přepisovat první podmínku s rokem (20) na aktuální rok, nahradíme tuto část vzorcem, který vrátí aktuální rok a následně odečte 2000 čímž získáme aktuální rok (2020 – 2000 = 20). Jelikož funkce vrací aktuální rok, v roce 2021 nám funkce vrátí hodnotu 21. 

=KDYŽ(ZLEVA(F6;2)*1<=ROK(DNES())-2000;ZLEVA(F6;2)+2000;ZLEVA(F6;2)+1900) 

Měsíc narození 

V našem dalším vzorci získáme část rodného čísla, které obsahuje měsíc narození. Měsíc narození se nachází na 3. a 4. pozici rodného čísla. Abychom získali tuto hodnotu, použijeme funkci ČÁST(), která nám umožní získat 3. a 4. znak. Pro více detailu o této funkc i se podívejte na náš článek o této funkci. Pro naši potřebu vybereme zdrojový řetězec (F6) a následně zvolíme pozici, na které chceme začít (3) a počet znaků které chceme získat (2).

=ČÁST(F6;3;2)*1 

Ženy mají k měsíci narození přičtenou číslovku 50, a tak se na místo měsíců 01 – 12 zobrazují měsíce 51 –52. Tuto podmínku budeme muset ošetřit jako první, abychom mohli správně přeložit měsíc narození na datum, musíme v případě ženy odečíst hodnotu 50. Pokud by jste někdy potřebovali zjisti, zda rodné číslo patří muži nebo ženě, bylo by to v této části. Pouze by jste museli změnit výsledky podmínky na “Muž”, nebo “Žena”. 

=KDYŽ(ČÁST(F6;3;2)*1>50;ČÁST(F6;3;2)*1-50;ČÁST(F6;3;2)*1) 

Bohužel se nám celý výpočet opět zkomplikuje o část, kterou spousta jiných příkladů vesele ignoruje. V případě, že dojde k vyčerpání všech kombinací rodného čísla za lomítkem pro daný den, se u měsíce narození provádí další rozšíření. U žen jsou měsíce narození 71-82 a u mužů 21-32. Nezbude nám tedy nic jiného než do našeho vzorce zakombinovat další podmínky. 

Den narození 

To nejlehčí na konec. Den narození z rodného čísla je naštěstí opravdu pouze datum. A tak pomocí funkce ČÁST() získáme pouze poslední dva znaky rodného čísla před lomítkem, a převedeme je na číslo. 

=ČÁST(F6;5;2)*1 

Výsledná funkce 

 Nyní máme připravené 3 vzorce pro jednotlivé části rodného čísla. Zjistili jsme rok narození, měsíc i den. Pomocí funkce DATUM() nyní můžeme všechny tři tyto hodnoty zkombinovat do jednoho vzorce a zjistit tak datum narození z rodného čísla.

DATUM(rok;měsíc,den) 

Po zkombinování jednotlivých hodnot máme vzorec, který sice není moc dobře čitelný, ale dělá přesně to co potřebujeme.  

=DATUM(KDYŽ(ZLEVA(B1;2)*1<=ROK(DNES())-2000;ZLEVA(B1;2)+2000;ZLEVA(B1;2)+1900);(KDYŽ(ČÁST(B1;3;2)*1>50;KDYŽ(ČÁST(B1;3;2)*1>50;ČÁST(B1;3;2)*1-70;ČÁST(B1;3;2)*1-50);KDYŽ(ČÁST(B1;3;2)*1>20;ČÁST(B1;3;2)*1-20;ČÁST(B1;3;2)*1)));ČÁST(B1;5;2)*1) 

 Jako malý bonus si můžeme vytvořit rychlou funkci, která nám vrátí pohlaví z rodného čísla. Výpočet je jednoduchý. Jak jsme si ukázali v části s výpočtem měsíce narození, tak víme že ženy mají k měsíci narození připočítanou hodnotu +50. Stačí tedy testovat zda je daná hodnota na této pozici >50.

=KDYŽ(ČÁST(B1;3;2)*1>50;"Ž";"M")