How to Auto Convert Malaysian IC Number to Birthdays Using Excel (Updated to Support Millennials)
I wrote this an article about how to automatically convert Malaysian IC Number to Birthdays five years ago. I was surprised that people recently commented that it didn't support those born after the year 2000. I'm responsible of putting the original information online so I'm obliged to update it if it didn't work. So here I am giving you the updated version.
You can play with the excel sheet here to see how it works.
The excel formulas that I used above are as below.
YYYY-MM-DD Format
=CONCATENATE(IF(LEFT(A1,2)<RIGHT(YEAR(TODAY()),2),"20","19"),LEFT(A1,2),"-",MID(A1,3,2),"-",MID(A1,5,2))
DD-MM-YYYY Format
=CONCATENATE(MID(A1,5,2),"-",MID(A1,3,2),"-",IF(LEFT(A1,2)<RIGHT(YEAR(TODAY()),2),"20","19"),LEFT(A1,2))
There are two parts to the formula. Depending on the format, the sequence will be different. Generally, I extract the month, day and year. I put a logic to decide if it should put 19 or 20 in front or not. What I did was to check if the two digit signifying the year is smaller than this year or not. Eg : 17 is smaller than 18 so 17 must be in 2017. Something like that.
If there's the need for me to explain my formula above further, I'll do update this article. Let me know in the comments section.
HI.. thank you for your knowlegde sharing..
ReplyDeletebut there is a problem, when i copy the formula to my excel. it popup showed error. why?