Extract date from string as MM/DD/YY

[Antor78]

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]

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

 

[jasonb75]

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.

=IF(LEFT(B3,2)="17",--(20&TEXTJOIN("/",1,MID(B3,{5,7,3},2))),"")

 

[Antor78]

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]

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

 

[Antor78]

=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]

Can you post the barcode that is giving that error? It worked fine when I tested it.

 

[Antor78]

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]

My bad, I just realised my error. This should do it.

=IF(LEFT(B3,2)="17",DATEVALUE(TEXT(MID(B3,3,6),"2\000\/00\/00")),"")

 

[Antor78]

My bad, I just realised my error. This should do it.

=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]

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