Henkilötunnus Excelissä

Blogiin tulleen kysymyksen innoittamana päätin kirjoittaa postauksen henkilötunnuksen käytöstä Excelissä. Kuten kaikki varmasti tietää, niin henkilötunnus (ent. sosiaaliturvatunnus) koostuu syntymäpäivästä, välimerkistä, joka osoittaa syntymävuosisadan, sekä loppuosasta.

Sen vuoksi pienellä kikkailulla tämän pohjalta voidaan laskea henkilön syntymäpäivä ja esimerkiksi ikä. Jos tulit etsimään vastausta siihen, kuinka laskea ikä henkilötunnuksen perusteella excelissä ja sinulla on perustaidot hallussa, niin tällä kaavalla pääset alkuun:

Iän laskeminen englanniksi / suomeksi:

=IF(MID(B4;7;1)=”-”;YEARFRAC(E4;DATE(”19″&MID(B4;5;2);MID(B4;3;2);LEFT(B4;2));1);IF(MID(B4;7;1)=”A”;YEARFRAC(E4;DATE(”20″&MID(B4;5;2);MID(B4;3;2);LEFT(B4;2));1);NA()))

=JOS(POIMI.TEKSTI(B4;7;1)=”-”;VUOSI.OSA(E4;PÄIVÄYS(”19″&POIMI.TEKSTI(B4;5;2);POIMI.TEKSTI(B4;3;2);VASEN(B4;2));1);JOS(POIMI.TEKSTI(B4;7;1)=”A”;VUOSI.OSA(E4;PÄIVÄYS(”20″&POIMI.TEKSTI(B4;5;2);POIMI.TEKSTI(B4;3;2);VASEN(B4;2));1);PUUTTUU()))

Syntymäosan muuttaminen päivämääräksi englanniksi / suomeksi:

=IF(MID(B4,7,1)=”-”,DATE(”19″&MID(B4,5,2),MID(B4,3,2),LEFT(B4,2)),
IF(MID(B4,7,1)=”A”,DATE(”20″&MID(B4,5,2),MID(B4,3,2),LEFT(B4,2)),NA()))

=JOS(POIMI.TEKSTI(B4;7;1)=”-”;PÄIVÄYS(”19″&POIMI.TEKSTI(B4;5;2);POIMI.TEKSTI(B4;3;2);VASEN(B4;2));
JOS(POIMI.TEKSTI(B4;7;1)=”A”;PÄIVÄYS(”20″&POIMI.TEKSTI(B4;5;2);POIMI.TEKSTI(B4;3;2);VASEN(B4;2));PUUTTUU()))

Muista formatoida solu päivämääräksi, jos soluun tulee viisinumeroinen luku.

kokonaisuus

Tämän pitkän ja tuskallisen näköisen kaavan logiikka etenee seuraavasti:

(on muuten pisin tässä blogissa julkaistu kaava tähän mennessä!)

  1. Tarkistetaan, onko seitsemäs merkki ”-” vai ”A”, eli syntymävuosisata
  2. Jos ”-”, syntymäpäivä saadaan kaavalla DATE(”19″&MID(B4;5;2);MID(B4;3;2);LEFT(B4;2))
  3. Ikä lasketaan kaavalla YEARFRAC(E4;DATE(”19″&MID(B4;5;2);MID(B4;3;2);LEFT(B4;2));1), jossa solussa E4 on päivämäärä, jona ikä halutaan. Voit korvata sen kaavalla NOW() jos haluat laskea iän tänään.
  4. Jos seitsemäs merkki on ”A”, henkilö on syntynyt tällä vuosituhannella ja syntymäpäivä saadaan kaavalla DATE(”20″&MID(B4;5;2);MID(B4;3;2);LEFT(B4;2))
  5. Soluun tulee täten henkilön ikä vuosina laskettuna solussa B4 olevan hetun perusteella solussa E4 olevana päivänä

Huomion arvoisia asioita:

Valitettavasti Excel sopii huonosti ennen 1900 vuotta olleiden päivämäärien käsittelyyn, joten tämä kaava ei sovellu 1800-luvulla syntyneiden henkilöiden (esim. Mannerheim ja Sibelius) henkilötunnusten käsittelyyn. Näissä tapauksissa (ja kaikissa, joissa hetun 7. merkki on jotain muuta kuin ”-” tai ”A”) kaava palauttaa virheen #N/A.

Muistathan että henkilötunnusta voi käyttää henkilön yksilöimiseen, mutta ei tunnistamiseen! Lisää aiheesta täältä: http://www.hs.fi/kotimaa/a1477187628597

Mikä on sinun henkilötunnuksesi? Ethän kirjoittaisi sitä kuitenkaan mihinkään julkisesti?

9 ajatusta aiheesta “Henkilötunnus Excelissä”

  1. Jos saat koodin kopioimalla #NAME errorin niin kirjoita koodin ” ” merkit uudelleen. Ainakin Excel 365 versiolla ne olivat väärän malliset.

  2. On olemassa sääntö, että henkilötunnuksen loppuosassa xx0x, xx2x, xx4x, xx6x, xx8x ovat naiset loppuosia ja vastaavasti taas xx1x, xx3x, xx5x, xx7x, xx9x ovat miesten loppuosia. Eli henkilötunnuksen 10. merkki määrittää onko kyseessä mis vai nainen. Millainen kaava tarvitaan, että saadaan näkymään onko henkilötunnus miehen vai naisen?

    1. Hei!

      =IF(ISODD(MID(A1;10;1))=TRUE;”Mies”;”Nainen”)

      Tämä kaava katsoo, onko toiseksi viimeinen luku pariton, ja jos on, niin antaa arvon Mies, ja jos ei, niin antaa arvon Nainen.

      Suomenkielisessä versiossa kaava menisi:
      =JOS(PARITON(POIMI.TEKSTI(A1;10;1))=TOSI;”Mies”;”Nainen”)

      Kerro, jos tulee ongelmia!

  3. Moikka! Mikä tuon iän/syntymäajan laskemisen kaava henkilötunnuksesta on suomenkielisessä Excelissä? 🙂

    1. Tai saisiko erottelun hetusta tehtyä jotenkin yksinkertaisemmin niin, että saa pelkästään syntymävuoden erilliseksi? Se riittäisi datana tarpeisiini 🙂

    2. Hei! Pelkän syntymävuoden saa poimittua suomeksi:
      =JOS(POIMI.TEKSTI(B4;7;1)=”-”;”19″&POIMI.TEKSTI(B4;5;2);
      JOS(POIMI.TEKSTI(B4;7;1)=”A”;”20″&POIMI.TEKSTI(B4;5;2);PUUTTUU()))

      Ja englanniksi:
      =IF(MID(B4,7,1)=”-”,”19″&MID(B4,5,2),
      IF(MID(B4,7,1)=”A”,”20″&MID(B4,5,2),NA()))

  4. Onko tästä functiosta suomenkielistä versiota ? Ainakin mun excel, ilmoittaa virhettä kun kopion tuon ”koodin” vaikka päivittäisin ; -merkit.

Kommentoi

Sähköpostiosoitettasi ei julkaista. Pakolliset kentät on merkitty *