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, 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.

Comments

  1. UnknownJanuary 14, 2019 at 8:41 PM

    HI.. thank you for your knowlegde sharing..
    but there is a problem, when i copy the formula to my excel. it popup showed error. why?

    ReplyDelete
  2. mohd akromJuly 14, 2024 at 10:58 AM

    Thank you for the info. Really helps me.

    ReplyDelete

Post a Comment

Popular posts from this blog

How to Prepare for a Table Topics Compeition?

-
Image
Original photo by  Kane Reinholdtsen  on  Unsplash "How do I prepare for the upcoming Table Topics competition?" was the question that popped up in my head before my first competition. After winning my second consecutive district level table topic contest , I believe I have something to share. We are going to cover the possible topics in the competition, how to practice and what to read. If you have one or two weeks before your competition, I recommend you to read  5 Tips For Your Next Table Topic Contest . If you have more time than that, please read on! If you prefer videos, please follow this link to the video version of this article.  Table of Contents Possible Topics in The Competition How to Practice Practice Understanding the Topic Practice Sharpening Your Points Practice Breaking Your Usual Patterns Practice Handling Hard Topics What to Read Possible Topics in The Competition You might ask, "How do you prepare for something unknown?". Well, a Table Topi...
Read more

How to Solve ModuleNotFoundError: No module named 'tencentcloud' on Tencent Serverless Cloud Function?

-
Image
Introduction You are excited to use Tencent Serverless Cloud Function to do your automation. You are following the Tencent API Explorer  and you are ready to rock yet when you test your Python code; you encountered  ModuleNotFoundError: No module named 'tencentcloud' . This is a guide on how to solve this error and explanations of why you need to do those steps.  How to solve this error According to Tencent Cloud's Serverless Cloud Function's  Dependency Installation guide , there are two ways to solve this problem.  Package the dependency libraries and code together for upload - I haven't tried this so if you did and it works, please feel free to write a post and comment here. Use dependency management tools. Use dependency management tools to solve this error I'm going to use the same steps as shown in the guide. I will add pictures and edit the steps so that it will be easier for you.  Log in to the SCF console and select Function Service on the left s...
Read more