Thread: convert id number into date

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

2. 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. Hi

What happens if you're only 10 years old?

zeddy

5. 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. 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. 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
•