# Thread: Formula needed to extract a date from a string

1. ## Formula needed to extract a date from a string

I need a formula that will allow me to extract the first 8 characters of a text string and turn it into a date. Below is an example of the string that I need help with

2013011606110401M1

2. Try the following

2013011606110401M1 in cell X16, the formula would be:
=CONCATENATE(MID(X16,5,2),"/",MID(X16,7,2),"/",LEFT(X16,4)) yields 01/16/2013

Crude, but I am sure there is a much better way to do this.

HTH,
Maud

3. Thanks Maud. I was hoping to find something that would turn it into a date that I could use in formulas

4. Sunshine,
In the above example, if the formula was entered in cell X20 and you wanted to add 1 day to it in the adjacent cell, Y20 = X20 + 1, the result will be the serial number 41291. If you format Y20 as a date, it will then display as 01/17/2013.

Good Luck,
Maud

5. I found one through Google. It is (using cell A1):

=DATE(2000+MID(A1,3,2),MID(A1,5,2),MID(A1,7,2))

6. Yes, much nicer!

7. Why do you need to add 2000!

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))

#### Posting Permissions

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