# Extract date from string as MM/DD/YY

#### Antor78

##### New Member
Hi all I need help to extract date as MM/DD/YY from the following :

17200131101234567 (date is always between prefix "17" and infix "10") in this example the date is 200131 = 01/31/20 (MM/DD/YY)

I tried the following to extract the date =IF(LEFT(B3,2)="17",MID(B3,3,6),"") and resulted in 200131. How can I format it as part of the extracting formula to display as MM/DD/YY ?

#### Antor78

##### New Member
When you only have a 2 digit year excel guesses as to whether is should be past or future based on the current date.

Does the formula really need to work for dates that far into the future or were you simply testing random strings?
Sadly it does. I am parsing GS1 formatted barcodes and those dates are for expiration dates on new product which can expired ten years from now so I am getting a lot of 2031 product

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### jasonb75

##### Well-known Member
This quick change will work as long as the dates don't go outside of the range of 1/1/2000 to 12/31/2099.
Excel Formula:
``=IF(LEFT(B3,2)="17",--(20&TEXTJOIN("/",1,MID(B3,{5,7,3},2))),"")``

#### Antor78

##### New Member
When you only have a 2 digit year excel guesses as to whether is should be past or future based on the current date.

Does the formula really need to work for dates that far into the future or were you simply testing random strings?
Hi, Yes it does. I am parsing GS1 barcodes and I have lots of new product with expirations dates 10 years into the future so a lot of 2031 product

#### Antor78

##### New Member
Weird. I replied and refreshed my page but didn't see my response so I posted it again.

#### Antor78

##### New Member

=IF(LEFT(B3,2)="17",--(20&TEXTJOIN("/",1,MID(B3,{5,7,3},2))),"")
I am getting a #Value! error. I copied it straight from here.

#### jasonb75

##### Well-known Member
Can you post the barcode that is giving that error? It worked fine when I tested it.

#### Antor78

##### New Member

Can you post the barcode that is giving that error? It worked fine when I tested it.
Is just this with a 31 for year. I am trying to upload the mini sheet but I am having issues finding a way to get the newly installed add in to show up on my custom ribbon. As soon as I figure that out I will upload it.
 17310930101234578

#### jasonb75

##### Well-known Member
My bad, I just realised my error. This should do it.
Excel Formula:
``=IF(LEFT(B3,2)="17",DATEVALUE(TEXT(MID(B3,3,6),"2\000\/00\/00")),"")``

#### Antor78

##### New Member
My bad, I just realised my error. This should do it.
Excel Formula:
``=IF(LEFT(B3,2)="17",DATEVALUE(TEXT(MID(B3,3,6),"2\000\/00\/00")),"")``
That took a spin haha this awesome!!! and with datevalue you didn't need to coarse the text string with --. Supercool Thank you so much. and yes I will keep in mind the date range 2000 to 2099 which still applies to this new formula. Thank you.

#### jasonb75

##### Well-known Member
That took a spin haha
With the first textjoin formula the 2 digit year meant that the date needed to be extracted in local format (mm/dd/yy) to prevent errors. I forgot about this when I changed it to 4 digit year which meant that the problem barcode that you provided was trying to extract a date of september 31

Replies
3
Views
84
Replies
2
Views
269
Replies
2
Views
310
Replies
24
Views
1K
Replies
15
Views
612

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,118
Messages
5,768,219
Members
425,460
Latest member
Astros1243

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back