Calc : Auto Copy Specific Characters to Another Cell

[hafiz_yahya]

Hi,
I have difficulty figuring this thing out :

let say I have a column name IDNumber and the first value is 810506-00W33466 :

| IDNumber |

810506-00W33466

The first six characters (810506) is a date of birth which is started by Year followed by Month and Day :

   810506 = 6 May 1981

The problem is I need a formula that can automatically copied the first six characters into another column (let say DateOfBirth) as a date format. So it suppose to show like this :

| DateOfBirth |

06 May 1981

Hope anyone can help me on this one coz currently i’m dealing with a spreadsheets that contain more than 500,000+ rows of data! Thanks.

Bob
Kyoto Japan

[sci_citation]

I would use something like this:

=DATE(MID(cell_ID;1;2);MID(cell_ID;3;2);MID(cell_ID;5;2))

explanation:

DATE(year ; month ; day)

MID(cell_ID ; start_position ; number_of_characters)

[hafiz_yahya]

Thank alot

[B.Horvat]

If IdNumber in cell A2 in column DateOfBirth, cell B2 insert formula =DATE(MID(A2;1;2);MID(A2;3;2);MID(A2;5;2)). Select appropriate date format to column DateOfBirth.

[hafiz_yahya]

Works like a magic. Thanks alot