Cara Mengambil Tanggal Lahir dari NIK di Microsoft Excel

Summary

This tutorial demonstrates how to extract birthdates from NIK data in Microsoft Excel. The first step involves separating male and female data. For males, a formula using Excel’s `MID` function is applied to extract the day, month, and year from the NIK. For females, a similar formula is used, with an adjustment made by adding 40 to the year extraction due to gender-specific encoding. The tutorial concludes by guiding users on how to apply the formulas to all data entries, offering an efficient solution for processing NIK birthdate information.

Takeaways

• 😀 Separate the male and female data before applying formulas.
• 😀 Use the 'MID' function to extract the day, month, and year from the NIK for males.
• 😀 For males, the formula to extract the birth date is: =TEXT(MID(NIK, 7, 2) & "/" & MID(NIK, 9, 2) & "/" & MID(NIK, 11, 2), "DD/MM/YY").
• 😀 Drag the formula down after inputting it in the first cell to apply it to all rows for male data.
• 😀 For female data, copy the formula used for males and paste it into the female data column.
• 😀 Add 40 to the NIK value for females to adjust for gender-specific coding.
• 😀 The final formula for females after adjustment would be: =TEXT(MID(NIK + 40, 7, 2) & "/" & MID(NIK + 40, 9, 2) & "/" & MID(NIK + 40, 11, 2), "DD/MM/YY").
• 😀 The tutorial focuses on extracting birth dates from the NIK in Excel.
• 😀 The 'MID' function is used to extract specific segments of the NIK for day, month, and year.
• 😀 The formula for both male and female data follows a similar structure, with a gender-specific adjustment for females.

Q & A

What is the main purpose of this tutorial?

The tutorial demonstrates how to extract a person's birth date from their NIK (Nomor Induk Kependudukan) number in Microsoft Excel.

How should the data be organized before applying the formula?

The data should be separated into two columns: one for male data and one for female data.

What function is used to extract specific parts of the NIK in Excel?

The MID function is used to extract specific parts of the NIK (the day, month, and year).

Can the formula for male data be directly applied to female data?

No, the formula for female data needs a slight adjustment. Specifically, you add '40' in the formula to account for the difference in NIK formatting for females.

What are the key components of the formula used to extract the birth date?

The formula uses the MID function to extract the day (7th and 8th digits), month (9th and 10th digits), and year (11th and 12th digits) from the NIK, and then combines them into a date format using the TEXT function.

How do you apply the formula to multiple rows in Excel?

After entering the formula in the first cell, you can drag the formula down to apply it to the entire column.

What should be done after copying the formula for female data?

After copying the formula, you need to add the number '40' before the first closing parenthesis of the formula to adjust the NIK for female individuals.

What does the '40' added to the formula for female data represent?

'40' is added to the NIK number to adjust for the difference in the numbering system for females in the NIK, as female NIK numbers typically differ from male ones.

Why is the TEXT function used in the formula?

The TEXT function is used to format the extracted parts of the NIK into a proper date format (DD/MM/YY) once the day, month, and year are extracted.

What happens if the formula is not adjusted for female data?

If the formula is not adjusted for female data, it will likely produce incorrect or invalid birth dates because the NIK number for females differs from that of males.