Civil Registration Number

[Hans Knudsen]

I have tried to create a formula to calculate birthday from a (Danish) Civil Registration Number. It seems to work fine in most cases, but not all. I have described the rules and what I have done so far in the attached file. My question appears in J32 and downwards but I think you will need to read all the comments.

There are two named formulas fs (short for fifth and sixth digit) and s (short for seventh digit)

 

[Hans Knudsen]

Oh - I completely forgot that first date in Excel is January 1st 1900. I think that explains what I asked about.
Is there any way around that?

 

[vletm]

Hans Knudsen​

Could You use this?

 

[Hans Knudsen]

vletm

Yes I could.
How elegant - thank you very much.

 

[vletm]

Hans Knudsen​

What is 'this problem'?
Didn't You originally search something from A-column?
... and then above ... You wrote something ... number of years from birthday.
Are those same?

 

[Hans Knudsen]

vletm

I am more than happy for what you have already given me. No further questions.

 

[Peter Bartholomew]

Hello Hans
Now I need to check out @vletm's solution but meanwhile here is a fully λ-ised version!

DOBλ(CPR)
= LET(
    d,     LEFT(CPR, 2),
    m,     MID(CPR, 3, 2),
    y,     MID(CPR, 5, 2),
    tail,  RIGHT(CPR, 4),
    digit, LEFT(tail, 1),
    year,  MAP(y, digit, ExpandYearλ),
    CONCATENATE(d, "/", m, "/", year)
  )
 
ExpandYearλ(y, digit)
=LET(
    group,     {0; 4; 5; 9},
    threshold, {0; 36; 57; 36},
    deduct,    {1; 1; 2; 1},
    changes,   IF(threshold < yr, {1; 1; 2; 1}),
    change,    XLOOKUP(VALUE(digit), group, changes, , -1),
    century,   20 - change,
    century & yr
)

Peter

 

[bosco_yip]

Here is my formula solution for all Excel versions

In C3, formula copied down:

=REPLACE(TEXT(TEXT(LEFT(A3,6),"00\/00\/00"),"mm/dd/yy"),7,,18+INDEX({1,1,1;2,1,1;2,2,0;2,1,1},MATCH(0+MID(A3,7,1),{0;4;5;9}),MATCH(0+MID(A3,5,2),{0,37,58})))

 

[Peter Bartholomew]

Sorry folks. I have just noticed an error in my Lambda function
ExpandYearλ(yr, digit).
I had tested the function with numeric parameters but on the sheet it is called with the numbers held as text. I had converted 'digit' but not 'yr'.

=LET(
    group,     {0; 4; 5; 9},
    threshold, {0; 36; 57; 36},
    deduct,    {1; 1; 2; 1},
    changes,   IF(threshold < VALUE(yr), {1; 1; 2; 1}),
    change,    XLOOKUP(VALUE(digit), group, changes, , -1),
    century,   20 - change,
    century & yr
)

 

[Hans Knudsen]

Thank you very much to both bosco_yip and Peter Bartholomew. I have really some work to do here in trying to understand your respective formulas. I wonder if I ever shall be able to understand the Lambda solution.
I know I should have stated the following from the start, but I forgot to. The CPR-may be stated either with or without a hyphen after the first 6 digits that is xxxxxxxxxx or xxxxxx-xxxx with a hyphen actually entered and not appearing as part of formatting. One more thing, when numbers are imported into Excel from other systems for example 0104909995 often will be converted to a 9 digit number 104909995. It seems to me that Peters Lambda-construction can handle the hyphen-thing but not the zero as first digit thing. Could this be handled too?

 

[Hans Knudsen]

Here is my formula solution for all Excel versions

In C3, formula copied down:

=REPLACE(TEXT(TEXT(LEFT(A3,6),"00\/00\/00"),"mm/dd/yy"),7,,18+INDEX({1,1,1;2,1,1;2,2,0;2,1,1},MATCH(0+MID(A3,7,1),{0;4;5;9}),MATCH(0+MID(A3,5,2),{0,37,58})))

View attachment 84793

 

[Peter Bartholomew]

Hans, it is not any lack of knowledge that makes Lambda difficult, it is the weight of your existing knowledge of traditional methods. Lambda functions do not dictate the formula; any set of logical steps you may suggest can be implemented.

In the present case, a possible approach might be to assume that the text versions with the hyphen "-" are correctly formatted whilst the numerical formats may well be in error. Therefore, a possible solution would be to convert any number to 10 digits plus the embedded hyphen.

= LET(
    s,     TEXT(CPR, "000000-0000"),
    d,     LEFT(s, 2),
    m,     MID(s, 3, 2),
    y,     MID(s, 5, 2),
    tail,  RIGHT(s, 4),
    digit, LEFT(tail, 1),
    year,  MAP(y, digit, ExpandYearλ),
    CONCATENATE(d, "/", m, "/", year)
  )

From that point, everything proceeds as before. In fact, I would claim it to be a strength of LAMBDA/LET that such corrections are reasonably easy to implement and there is only one instance of the formula to be edited.