How old is the owner of JMBG?

The unique identity number of citizens (JMBG) was created in the Socialist Federal Republic of Yugoslavia, and is still used today in the Republic of Serbia, for the purpose of identifying citizens. It is a mandatory datum in the identity card, driver’s license, health card, passport and all other important documents. Its first 9 digits indicate the date of birth. How to calculate the age of the JMBG holder based on this data?

Let’s start from a simple table with 10 fictional names and their JMBG numbers. JMBG consists of 13 digits. Twelve are mandatory and the thirteenth is a control number, which is calculated using a special algorithm. In this example, the numbers are entered randomly (and are probably incorrect, but will serve the purpose).

First, either through the Data Validation option or by checking the basis condition, with the help of the IF and LEN functions, it should be determined that JMBG contains exactly 13 digits. Then, let’s move on…

To calculate the date of birth based on JMBG, enter the formula:

=LEFT(C2,2)

The month is calculated using the formula:

=MID(C2,3,2)

When calculating the year, we need to use a slightly more complex expression. Within JMBG, those born in the 20th century will have fifth digit nine, while those born in the 21st century have the fifth digit zero. Let’s write the formula:

=IF(NUMBERVALUE(MID(C2,5,1))=9,”1″&MID(C2,5,3),”2″&MID(C2,5,3))

The NUMBERVALUE function converts the fifth digit to a number and then compares it to nine. If we want to simplify the expression, we can also change the condition and declare the nine as a string. In that case, the formula will have the following form:

=IF(MID(C2,5,1)=”9″,”1″&MID(C2,5,3),”2″&MID(C2,5,3))

We calculate the date of birth by combining the year, month and day:

=DATE(F2,E2,D2)

To calculate the current day, we can use the formula where we specify only the TODAY() function.

Age is the difference between today’s date and date of birth. In the past, we could use the DATEDIF function for this purpose, and write the formula:

=DATEDIF(G2,H2,”Y”)

This function is no longer supported as standard Excel function. We can calculate years using the YEARFRAC function, then round the value using the INT function:

=INT(YEARFRAC(G2,H2))

This would be a way to calculate years based on JMBG using traditional Excel functions. In practice, you might be better off using Power Query because it also supports dates older than 1/1/1900.