Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Oct 2012
    Location
    Kuala Lumpur, Malaysia
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    convert id number into date

    hi there, i want to know if there have any function to convert id number to date.

    For example, the id number is 801107

    - the first two number is (80) is refer to year of birth (1980)
    - the third and fourth number (11) is refer to month of birth (November)
    - the last two number (07) is refer to day of birth (07)

    I want to know how to convert this is id number when entered, it's automatically display 07 November 1980.

    Thank you for your help.

  2. #2
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi

    Assuming in A1: 801107, in B1 and format the cells "dd mmmm yyy", without the quotes.

    =DATE("19"&LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))

  3. The Following User Says Thank You to Kevin@Radstock For This Useful Post:

    maliq2805 (2013-11-02)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    What happens if you're only 10 years old?

    zeddy

  5. #4
    New Lounger
    Join Date
    Oct 2012
    Location
    Kuala Lumpur, Malaysia
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Kevin,

    Thanks. it's work perfectly.

    one more question, how about if the id have 8 number, for example 80110703

    the last two number (03) is refer to place of birth (01- johor, 02- kedah, 03- kelantan)

    i want in C1 will display "kelantan".

    thank you again.

    -maliq-

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    maliq,

    You could also take the formula that Kevin wrote and change to a user defined function. This will enable you to change the format in the cell to "dd mmmm yyyy" automatically.

    Code:
    Public Function Mydate(dt As Long)
    tYear = Left(dt, 2)
    tMonth = Mid(dt, 3, 2)
    tDay = Right(dt, 2)
    Mydate = Format(tMonth & "/" & tDay & "/19" & tYear, "dd mmmm yyyy")
    End Function
    The function is called by entering the formula =Mydate(A2) (see image)

    dateconversion.png

    How will you differentiate between the years 1911 and 2011?

  7. #6
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi

    Perhaps something along these lines.

    In B1: =DATE("19"&LEFT(A1,2),MID(A1,3,2),MID(A1,5,2)) & format as dd mmmm yyy
    In C1: =LOOKUP(RIGHT(A1,1)+0,{1,2,3},{"johor","kedah","ke lantan"})

    Alternatively use the VLOOKUP function.

    http://www.contextures.com/xlFunctions02.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •