Extract date from string as MM/DD/YY

Antor78

New Member
Joined
Aug 26, 2011
Messages
14
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
Joined
Aug 26, 2011
Messages
14
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
Joined
Dec 30, 2008
Messages
14,621
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 26, 2011
Messages
14
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
Joined
Aug 26, 2011
Messages
14
Weird. I replied and refreshed my page but didn't see my response so I posted it again.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,621
Office Version
  1. 365
Platform
  1. Windows
Can you post the barcode that is giving that error? It worked fine when I tested it.
 

Antor78

New Member
Joined
Aug 26, 2011
Messages
14

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
14,621
Office Version
  1. 365
Platform
  1. Windows
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")),"")
 
Solution

Antor78

New Member
Joined
Aug 26, 2011
Messages
14
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
Joined
Dec 30, 2008
Messages
14,621
Office Version
  1. 365
Platform
  1. Windows
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 :oops:
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top