Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Calculate age from personal number when birth year only has two numbers

Tags:

I know there is A LOT of questions resembling this. BUT I'm not asking the same!

My problem is that all of the questions which I've looked at has birthdays with the whole year, fx 04/05/1971 (format: %d/%m/%Y).

The birthdays in my data is Danish CPR-numbers (personal identification numbers), and they look like this:

   ID
1901912222
0110841111
0404143333
1602032444

NB: Those dates are examples. I have thousands of rows and it's people in all ages, also above 100 (but most often no more than 17).

1st and 2nd number: day of birth 3rd and 4th number: month of birth 5th and 6th number: year of birth The last four = Sequential number.

So this gives me the birthdays (and ages):

   ID         birthdate      age
1901912222    19/09/91        26
0110841111    01/10/84        33
0404143333    04/04/14        103
1602024444    16/02/02        15

So the format is: %d%m%y[the sequential number of 4 digits]

So the last four digits (the sequential number) also has some information. They tell if the person is fx 3 or 103 years old (now that I don't have the year). See the image for description:

Birth year and sequential number

I don't know if it's any help, but I have the Excel code:

=YEAR(NOW())-1-IF(DATE(YEAR(NOW());MID(D12;3;2);LEFT(D12;2))<=NOW();MID(D12;5;2)+IF(LEFT(RIGHT(D12;4);1)*1<=3;1900;IF(AND(LEFT(RIGHT(D12;4);1)*1=4;MID(D12;5;2)*1<=36);2000;IF(AND(LEFT(RIGHT(D12;4);1)*1=4;MID(D12;5;2)*1>=37);1900;IF(AND(LEFT(RIGHT(D12;4);1)*1>=5;LEFT(RIGHT(D12;4);1)*1<=8;MID(D12;5;2)*1<=57);2000;IF(AND(LEFT(RIGHT(D12;4);1)*1>=5;LEFT(RIGHT(D12;4);1)*1<=8;MID(D12;5;2)*1>=58);1800;IF(AND(LEFT(RIGHT(D12;4);1)*1=9;MID(D12;5;2)*1<=36);2000+MID(D12;5;2);1900))))))-1;MID(D12;5;2)+IF(LEFT(RIGHT(D12;4);1)*1<=3;1900;IF(AND(LEFT(RIGHT(D12;4);1)*1=4;MID(D12;5;2)*1<=36);2000;IF(AND(LEFT(RIGHT(D12;4);1)*1=4;MID(D12;5;2)*1>=37);1900;IF(AND(LEFT(RIGHT(D12;4);1)*1>=5;LEFT(RIGHT(D12;4);1)*1<=8;MID(D12;5;2)*1<=57);2000;IF(AND(LEFT(RIGHT(D12;4);1)*1>=5;LEFT(RIGHT(D12;4);1)*1<=8;MID(D12;5;2)*1>=58);1800;IF(AND(LEFT(RIGHT(D12;4);1)*1=9;MID(D12;5;2)*1<=36);2000+MID(D12;5;2);1900)))))))

I really do hope you can help me with this problem!

like image 470
Louise Sørensen Avatar asked Dec 29 '17 12:12

Louise Sørensen


People also ask

What is the formula to calculate the age of a person?

Age of a Person = Given date - Date of birth. Ron's Date of Birth = July 25, 1985. Given date = January 28, 2021. Years' Difference = 2020 - 1985 = 35 years.

How do I calculate age in Excel without Datedif?

USING YEARFRAC FUNCTION: YEARFRAC function in Excel returns a decimal value that represents fractional years between two dates. We can use this function to calculate age.

How do I calculate age from a certain date?

The exact number of days in a year is 365.2422. Using 365.25 is an approximation. To calculate age using this method, first subtract the dates to calculate the number of days, then divide by 365.2422.


1 Answers

The hard part is extracting the actual birth date from the id. The following function does so by creating three arrays to look up either "19" or "20" depending on whether the the year is 00-36, 37-57, or 58-99. It returns the dates in the standard format "yyyy-mm-dd":

A <- c(rep("19",4),rep("20",6))
B <- c(rep("19",5),rep("20",4),"19")
C <- c(rep("19",5),rep("18",4),"19")
birthday <- function(code){
  day <- substr(code,1,2)
  month <- substr(code,3,4)
  year <- substr(code,5,6)
  snum <- 1+as.numeric(substr(code,7,7))
  prefix <- ifelse(as.numeric(year) <= 36,A[snum],ifelse(as.numeric(year)<=57,B[snum],C[snum]))
  year <- paste0(prefix,year)
  paste(year,month,day,sep = "-")
}

For example:

df <- data.frame(ID = c("1901912222","0110841111","0404143333","1602024444"))
df$BD <- birthday(df$ID)

Yielding:

          ID         BD
1 1901912222 1991-01-19
2 0110841111 1984-10-01
3 0404143333 1914-04-04
4 1602024444 2002-02-16

Once you have the birthday in standard 4-digit year format, it is easy enough to e.g. calculate age. See this question.

like image 78
John Coleman Avatar answered Sep 22 '22 11:09

John Coleman