Avatar billede RikkePrikke Mester
12. april 2019 - 11:58 Der er 30 kommentarer og
2 løsninger

Omregning af cpr til dato

Hej
Jeg (og mine kolleger) har søgt her inde og har delvist fået svar. Men der er noget vi ikke fortår:
Vi har de 6 første cifre af et cpr nummer som vi skal omregnet til dato:
Vi starter med at fjerne de sidste 4 cifre med formlen: =VENSTRE(A4;6)
Heraf får vi f.eks et resultat:111265 (dette er korrekt)

Herefter bruger vi nedenstående formel for at få en fødselsdags dato :
=HVIS(LÆNGDE(B5)=5;DATO(HØJRE(B5;2);MIDT(B5;2;2);VENSTRE(B5;1));HVIS(LÆNGDE(B5)=6;DATO(HØJRE(B5;2);MIDT(B5;3;2);VENSTRE(B5;2))))
Resultat :11-12-1965 Det er perfekt!

MEN:
Så kommer vi til et cpr nummer som kunne se sådan ud:
040680-3306 (opdigtet)
Heraf får vi resultatet:
406803
Som derefter ender med at se sådan ud:
09-09-1908
Altså det går galt allerede da 0'et fjerner sig, og det 1. ciffer ud af de 4 sidste cifre tages med i stedet for.

Jeg har forsøgt at formatere cellen brugerdefineret med 000000 og alle mulige andre formateringer, men intet hjælper.
Er der mon nogen som kan hjælpe??
Avatar billede martens Guru
12. april 2019 - 12:23 #1
Hej Rikke !

Jeg vil starte med at formatere kolonnen med dine inputdata ( 040680-3306 )  og ligeledes den kolonne, hvor du får sit udtræk ( 040680 ) som værende tekst.

Så bliver dine foranstillede "nuller" på deres plads ;o)

testet og fundet i orden !

mvh martens
Avatar billede Jan Hansen Ekspert
12. april 2019 - 12:24 #2
Hej
A4 er dit personnr
B4      =DATO(MIDT(A4;5;2);MIDT(A4;3;2);VENSTRE(A4;2))
og formater B4 som dato

Jan
Avatar billede RikkePrikke Mester
12. april 2019 - 13:05 #3
Hej Martens
Det virker desværre ikke.
Det virker hvis jeg sætter en ' foran, men det kan jeg jo ikke gøre på 200 celler!

Hej Jan
Det virker ikke, har jeg prøvet, der sker ikke en pind! - øv

Vh Rikke
Avatar billede Jan Hansen Ekspert
12. april 2019 - 13:20 #4
prøv denne
=DATO(MIDT(VENSTRE(TEKST(A4;"######");6);5;2);MIDT(VENSTRE(TEKST(A4;"######");6);3;2);VENSTRE(VENSTRE(TEKST(A4;"######");6);2))

Jan
Avatar billede Jan Hansen Ekspert
12. april 2019 - 13:25 #5
eller denne
=VENSTRE(TEKST(A4;"#######");2)& "-" & MIDT(TEKST(A4;"#######");3;2) &"-"& MIDT(TEKST(A4;"#######");5;2)
Avatar billede martens Guru
12. april 2019 - 13:58 #6
Jans og mit forslag virker som de skal...
Avatar billede Jan Hansen Ekspert
12. april 2019 - 14:08 #7
endnu en mulighed

=TEKST(VENSTRE(A1;6);"00-00-00")

alle fungerer hos mig og undgår en hjælpe kolonne!!
Jan
Avatar billede claes57 Ekspert
12. april 2019 - 14:46 #8
jeg ved ikke, om den regel stadig gælder, men tidligere var det, så løbenummer 0000-4999 viste, at person var født i 19xx, og ellers i enten 18xx eller 20xx (det et let at skælne, hvis xx > aktuelt år, så er person fra 18xx)
Der gik vist ged i den, da indvandrere med ukendt fødselsdato skulle have cpr-nummer.
Avatar billede store-morten Ekspert
12. april 2019 - 15:00 #9
Prøv:
A1: Cpr.nr

=VENSTRE(A1;2)&"-"&MIDT(A1;3;2)&"-"&MIDT(A1;5;2)+HVIS(VENSTRE(HØJRE(A1;4);1)*1<=3;1900;HVIS(OG(VENSTRE(HØJRE(A1;4);1)*1=4;MIDT(A1;5;2)*1<=36);2000;HVIS(OG(VENSTRE(HØJRE(A1;4);1)*1=4;MIDT(A1;5;2)*1>=37);1900;HVIS(OG(VENSTRE(HØJRE(A1;4);1)*1>=5;VENSTRE(HØJRE(A1;4);1)*1<=8;MIDT(A1;5;2)*1<=57);2000;HVIS(OG(VENSTRE(HØJRE(A1;4);1)*1>=5;VENSTRE(HØJRE(A1;4);1)*1<=8;MIDT(A1;5;2)*1>=58);1800;HVIS(OG(VENSTRE(HØJRE(A1;4);1)*1=9;MIDT(A1;5;2)*1<=36);2000+MIDT(A1;5;2);1900))))))
Avatar billede Mads32 Ekspert
12. april 2019 - 17:09 #10
Hej Rikke Prikke

Jeg har lavet et eksempel, der vise det du ønsker

https://www.dropbox.com/s/cyoxedk2ho1ox0n/omregne%20cpr%20til%20dato_mads32.xlsx?dl=0
Avatar billede RogerWilco Seniormester
13. april 2019 - 10:04 #11
Mellemregning:
=VENSTRE(HØJRE("0"&A4;10);6)

Mellemregning, hvis der kan være bindestreger i CPR-nummer:
=VENSTRE(HØJRE("0"&UDSKIFT(A4;"-";"");10);6)

Så behøver du ikke teste for 5 eller 6 cifre i datoformlen:
=DATO(HØJRE(B4;2);MIDT(B4;3;2);VENSTRE(B4;2))

Dertil kommer så århundrede-problematikken, som andre har nævnt.
Avatar billede RikkePrikke Mester
13. april 2019 - 12:25 #12
@Mads32: Jeg kan kun se dit resultat, og ikke hvordan du kommer frem til det?
@RogerWilco: Ja, det er lige DEN problematik jeg står med :/
@Jan Hansen: Jeg tester der ud, stadig uden held. Man burde kunne uploade et excel ark som alle kunne teste i så vi så det samme :)
Avatar billede RikkePrikke Mester
13. april 2019 - 12:30 #13
@store-Morten: Det giver resultatet 40-68-1903
Avatar billede store-morten Ekspert
13. april 2019 - 12:53 #14
Du skal bruge hele cpr.nr i A1
040680-3306 giver 04-06-1980
Avatar billede RikkePrikke Mester
13. april 2019 - 14:09 #15
@Store-Morten: Jeg er ikke sikker på jeg ved hvad du mener? Jeg henter cpr fra cellen A5(A1 i din formel) og det er et helt cpr.nr. 040680-33016.
Jeg har sat din formel direkte ind, blot ændret A1 til den celle nummeret står i. Når jeg gør det, bliver resultatet som jeg skrev, nemlig 40-68-1903
Jeg forstår det simpelthen ikke.
Nu prøver jeg at ligge det i dropboks
Avatar billede RikkePrikke Mester
13. april 2019 - 14:29 #16
Jeg har sat mit prøve ark i Dropboks, måske I kan se hvorfor alle jeres gode forslag ikke virker for mig! :/

https://www.dropbox.com/s/kg3zukd5uvw63c5/Excel%20cpr%20til%20dato.xlsx?dl=0
Avatar billede store-morten Ekspert
13. april 2019 - 15:02 #17
Det er ikke Cpr. numre der står i kolonne A?
Kolonne A er formateret til at vise "Personnummer"

Og når jeg tester på dine 6 numre får jeg:
Cpr-nummer ikke gyldigt
Cpr-nummer ikke gyldigt
Cpr-nummer ikke gyldigt
#VÆRDI!
#VÆRDI!
#VÆRDI!
Avatar billede RogerWilco Seniormester
13. april 2019 - 15:11 #18
Det er rådata i kolonne A den er gal med, da der ved indtastningen mangler det foranstillede nul. Det hjælper derfor ikke at sætte celleformatet til et bestemt udseende.

Har du prøvet den formel jeg foreslog til kolonne B?
=VENSTRE(HØJRE("0"&UDSKIFT(A4;"-";"");10);6)
Avatar billede store-morten Ekspert
13. april 2019 - 15:21 #19
=VENSTRE(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);2)&"-"&MIDT(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);3;2)&"-"&MIDT(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);5;2)+HVIS(VENSTRE(HØJRE(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);4);1)*1<=3;1900;HVIS(OG(VENSTRE(HØJRE(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);4);1)*1=4;MIDT(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);5;2)*1<=36);2000;HVIS(OG(VENSTRE(HØJRE(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);4);1)*1=4;MIDT(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);5;2)*1>=37);1900;HVIS(OG(VENSTRE(HØJRE(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);4);1)*1>=5;VENSTRE(HØJRE(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);4);1)*1<=8;MIDT(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);5;2)*1<=57);2000;HVIS(OG(VENSTRE(HØJRE(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);4);1)*1>=5;VENSTRE(HØJRE(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);4);1)*1<=8;MIDT(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);5;2)*1>=58);1800;HVIS(OG(VENSTRE(HØJRE(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);4);1)*1=9;MIDT(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);5;2)*1<=36);2000+MIDT(HVIS(LÆNGDE(A2)=9;"0"&A2;A2);5;2);1900))))))
Avatar billede Mads32 Ekspert
13. april 2019 - 15:51 #20
Hej Rikke

Vedlagt en version med forklaringer

Jeg ved ikke hvordan man på cpr nr skelner mellem folk født i 1900 tallet og folk født i 2000 tallene.

https://www.dropbox.com/s/ifjnxd4o89gmvi4/omregne%20cpr%20til%20dato_mads32_B.xlsx?dl=0
Avatar billede store-morten Ekspert
13. april 2019 - 16:05 #21
Skulle mene at formlen i #19 kan det hele :-) Cpr.nr i cell A2

Hvis de 4 sidste cifre er med.

Cpr.nr.  xxxxxx-xxxx
10 cifre  xxxxxxxxxx
9 cifre    xxxxxxxxx
Født i 1800, 1900 og 2000
Avatar billede Mads32 Ekspert
13. april 2019 - 16:41 #22
Hej Rikke
Jeg har lavet lidt resource på cpr nr., og har nu indarbejdet dette i det nye eksempel.

https://www.dropbox.com/s/gxrbr58fkn2hax3/omregne%20cpr%20til%20dato_mads32_C.xlsx?dl=0
Avatar billede Jan Hansen Ekspert
13. april 2019 - 17:07 #23
Prøv denne simple formel, virker på dit test ark
=TEKST(VENSTRE(A5;LÆNGDE(A5)-4);"00-00-00")

Jan
Avatar billede store-morten Ekspert
13. april 2019 - 17:22 #24
Spørgsmålet lyder:
"Omregning af cpr til dato?"

For at gøre dette, skal det være ægte cpr.nr. det regnes på.
Ikke tilfældige tal i en celle!

Se #17
6 poster der ikke er cpr. numre.

Så først skal der være cpr. numre i kolonne A før der kan beregnes.

Det giver ingen mening at forsøge at beregne en dato ud fra tilfældige tal.
Avatar billede store-morten Ekspert
13. april 2019 - 17:39 #25
Avatar billede bjarnehansen Seniormester
13. april 2019 - 18:28 #26
Avatar billede Jan Hansen Ekspert
13. april 2019 - 18:35 #27
Hej Rikke
Tror vi skal have opklaret noget:

1. du får dine "cpr-nr" fra andet system der  laver dem til en 9 eller 10 cifret nummer?
2. Du ønsker at fiske fødselsdato ud derfra?
3. Hvilket format vil du have fødselsdato-en i 00-00-00 eller 00-00-0000?

Hvis ovenstående er korrekte antagelser.
Løsning 1: Format 00-00-00
=TEKST(VENSTRE(A5;LÆNGDE(A5)-4);"00-00-00")

Løsning 2: format: 00-00-0000
Vil du  kunne i kolonne B ligge følgende formel
=TEKST(VENSTRE(A5;LÆNGDE(A5)-4);"000000") & "-" & HØJRE(A5;4)

så har du en Valid Cpr-nr og du kan store-Mortens løsning så du kan få sat århundrede på.

Jan
Avatar billede RikkePrikke Mester
13. april 2019 - 19:51 #28
Hvor er I bare MEGA seje! 1000 tak for alt jeres hjælp. I har brugt virkelig meget tid på mig min udfordring, det er satme prisværdigt!

Det jeg har fået til at virke, er Jan Hansens formel;
=TEKST(VENSTRE(A5;LÆNGDE(A5)-4);"00-00-00")
Den laver ballade hvis der er cpr. nr. fra år 2000, men dem har jeg heldigvis ikke nogen af endnu ;) Så den holder jeg mig til for nu. Men jeg skal lige ha tjekket Store-Mortens formler ud, for de er måske smartere på sigt ;)

Igen 1000 tak :)

Vh Rikke
Avatar billede Jan Hansen Ekspert
13. april 2019 - 21:15 #29
Velbekomme
Avatar billede RikkePrikke Mester
15. april 2019 - 15:50 #31
Kære alle seje folk
Vi fandt en løsning på dem så er født efter år 2000.
Det blev til en ekstra kolonne, men giver et perfekt resultat :)
Tjek det ud.

Og igen! 1000 tak for alle jeres timer :)

https://www.dropbox.com/s/8p59dhi2itni9fi/Excel%20cpr%20til%20dato%201.xlsm?dl=0

Vh Rikke
Avatar billede bjarnehansen Seniormester
15. april 2019 - 22:41 #32
http://www.kronsell.net/cpralder.htm
prøv at læse dette igen de to sidste cpr med samme datoer skal have forskellige 4 sidste ciffer
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester



IT-JOB

Cognizant Technology Solutions Denmark ApS

Senior DeltaV Engineer

Udviklings- og Forenklingsstyrelsen

Selvstændig projektleder til central samfundsopgave

AP Pension

Arkitekturchef