How to Auto Convert Malaysian IC Number to Birthdays Using Excel (Updated to Support Millennials)
Photo by Annie Spratt on Unsplash 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,...
Comments
Post a Comment