Auto Convert Malaysian IC Number to Birthdays Using Excel

For some reasons, you have an Excel sheet with a bunch of Malaysian IC numbers and you would like to convert them into birthdays for your CRM campaigns and etc. Here is an easy way for you to do it easily. By using Excel functions.

Let’s assume that the IC numbers are in the following format.
670607-88-8888

The birthday format needed by you is as below.
1967-06-07

The steps are simple.
1. Enter the following formula to the birthday column.
=CONCATENATE(“19″,LEFT(C2,2),”-“,MID(C2,3,2),”-“,MID(C2,5,2))

C2 is the column and the row where the IC number reside.The formula above will ad 19 in front of the year and put “-” as the separators of year, month and day. If the IC that you have involves people born after year 1999 then you are out of luck.

2. Copy the formula above to the rest of the remaining birthday cells through dragging or good old copy and paste.

Does this help you? Do let me know if it helps as it is the source of encouragement for me to write more.

Edit :
24/5/2014  – I’ve corrected the error in the original formula. If this new formula still gives you error, please try and retype all the ” symbols in the formula in your excel. It should solve your problem. It is a problem of conversion from web characters to excel characters.

Icon by iconmonstr

  • virtual

    i try your formula…but its appear this text #NAME?

    then i googled and i found this formula…
    =MID(C2,5,2)&”-“&MID(C2,3,2)&”-“&”19″&LEFT(C2,2)
    its really work…

    • http://www.weijie.info Chua Wei Jie

      Dear Virtual,

      Thank you very much for the feedback.
      I’ll check my formula again.
      In the mean time, I’m curious about the data that you are running my function against.

      I hope that the solution posted by you could help other readers that faced the same problem as you do.
      Thank you very much for your feedback.
      I’m looking forward to hear from you.

    • Fadzly

      tq bro.. now do anyone got formula for changing IC number to GENDER

      • http://www.weijie.info Chua Wei Jie

        I’m sorry for my late reply. I didn’t check my personal email as frequent as I wanted to.

        I did a quick and dirty example regarding your question based on my understanding as below..
        1. Odd numbers mean male and even numbers mean female. Please correct me if I’m wrong.
        2. I’ve tested the formula based on 670607-88-8888 though I don’t think the lack of hyphens is going affect anything.

        The formula is =if(mod(right(A1,1),2)=0,”F”,”M”). All you need to do is change the A1 in the formula to the cell you are referring to.

        I’ll follow up with a detailed post on this and send the link to you too. Please do let me know if my assumption is correct or not.

  • Michelle

    thanks virtual, u r a savior with ur formula…. hugs

    • http://www.weijie.info Chua Wei Jie

      I’m glad that I helped you.
      The pleasure’s mine.

  • Michelle

    thanks Chua Wei Jie for initiating… great help…

    • http://www.weijie.info Chua Wei Jie

      I’m glad that I helped you. The pleasure’s mine.