Mudah cara membuat tanggal lahir dari NIK di Excel | Tutorial Excel

Summary

This video explains how to extract a person's birth date from a NIK (Indonesian National Identity Number) using Excel formulas. The process involves extracting the day, month, and year from specific positions in the NIK using the MID function. Additionally, it includes logic to determine gender based on the NIK number (subtracting 40 for females) and formatting the year correctly. Finally, the date of birth is combined into a proper date format using the DATE function in Excel, making it easy to convert a NIK into a usable birth date.

Takeaways

• 😀 Extracting a person's birthdate from their NIK number involves using Excel formulas like MID and IF.
• 😀 The NIK number contains specific positions that represent the day, month, and year of birth.
• 😀 The day of birth can be extracted from positions 7 and 8 in the NIK number using the MID function.
• 😀 For gender determination, if the day number is greater than 40, the person is female; subtract 40 from the number to get the correct date.
• 😀 The month of birth is extracted from positions 9 and 10 in the NIK number using the MID function.
• 😀 The year of birth is extracted from positions 11 and 12 in the NIK number using the MID function.
• 😀 To handle years beyond 2023, you need to check if the extracted year plus 2000 exceeds 2023 and prepend '19' or '20' accordingly.
• 😀 To combine the extracted day, month, and year into a proper date format, use the DATE function in Excel.
• 😀 Use the IF function to modify the year if necessary to account for gender-based adjustments in the NIK number.
• 😀 The process enables the automatic extraction and correct formatting of birthdates based on the NIK, saving time in data processing.

Q & A

What is the purpose of the video?

The video explains how to use Excel formulas to extract a person's birth date from their Indonesian NIK (population identification number).

Which Excel function is used to extract the birth date from the NIK?

The 'MID' function is used to extract specific digits from the NIK to determine the birth date, month, and year.

What does the 'MID' function do in this context?

'MID' is used to extract a subset of digits from the NIK. For example, to extract the day, the 'MID' function pulls the 7th and 8th digits.

How do you identify the gender from the NIK?

Gender is determined by the 7th and 8th digits of the NIK. If the number is 40 or more, it indicates a female; otherwise, it's a male.

How is the year of birth determined from the NIK?

The year of birth is extracted from the 11th and 12th digits of the NIK using the 'MID' function, then adjusted based on the value of the year.

How do you handle two-digit year values that seem invalid (e.g., 99)?

If the year extracted from the NIK (e.g., 99) appears to be too large when added to 2000, the formula adds '19' to make it a valid year. If not, '20' is added.

What formula can be used to get the complete date in a proper format?

The 'DATE' function is used to combine the day, month, and year into a valid date format.

Why is it important to subtract 40 from the NIK number for females?

In the NIK system, if the extracted number for the gender part is 40 or more, it indicates a female. To correct this, 40 is subtracted to reflect the correct birth day.

How do you extract the month from the NIK?

The month of birth is extracted using the 'MID' function, by pulling the 9th and 10th digits from the NIK.

Why does the script add '19' or '20' before the year of birth?

The script checks if the year, after adding 2000, is greater than 2023. If so, it adds '19' to the year, otherwise it adds '20' to make the year valid.