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 ?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
With Power Query

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Changed Type", {{"Data", each Text.BetweenDelimiters(_, "17", "10"), type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Extracted Text Between Delimiters", "Data", Splitter.SplitTextByRepeatedLengths(2), {"Data.1", "Data.2", "Data.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Data.1", Int64.Type}, {"Data.2", Int64.Type}, {"Data.3", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Data.2", type text}, {"Data.3", type text}, {"Data.1", type text}}, "en-US"),{"Data.2", "Data.3", "Data.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date")
in
    #"Merged Columns"
 
Upvote 0
With Power Query

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Changed Type", {{"Data", each Text.BetweenDelimiters(_, "17", "10"), type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Extracted Text Between Delimiters", "Data", Splitter.SplitTextByRepeatedLengths(2), {"Data.1", "Data.2", "Data.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Data.1", Int64.Type}, {"Data.2", Int64.Type}, {"Data.3", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Data.2", type text}, {"Data.3", type text}, {"Data.1", type text}}, "en-US"),{"Data.2", "Data.3", "Data.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date")
in
    #"Merged Columns"
Awesome proposal. I should have mentioned that I need to keep this workbook as light and user friendly as possible so I am going to stay away from using power query, macros, or any VBA. Thank you so much for replying.
 
Upvote 0
With a formula, this will return the date serial which can be formatted as required.
Excel Formula:
=IF(LEFT(B3,2)="17",--TEXTJOIN("/",1,MID(B3,{5,7,3},2)),"")
 
Upvote 0
With a formula, this will return the date serial which can be formatted as required.
Excel Formula:
=IF(LEFT(B3,2)="17",--TEXTJOIN("/",1,MID(B3,{5,7,3},2)),"")
Great. I had just arrive to this one = =IF(LEFT(B3, 2) = "17", MID(B3, 5, 2)&"/"&MID(B3, 7, 2)&"/"&MID(B3,3,2), "") which does the MM/DD/YY but it doesn't offer the flexibility of using the format cells date options. I like your better :) thank you so much.
 
Upvote 0
With a formula, this will return the date serial which can be formatted as required.
Excel Formula:
=IF(LEFT(B3,2)="17",--TEXTJOIN("/",1,MID(B3,{5,7,3},2)),"")

What does -- infront of Textjoin does? and what does code=xls? and [/Code] does?

Thank you!
 
Upvote 0
It coerces the text string back to a number so that it can be formatted.

They tell the forum software that the text between them is a formula so that it can be formatted properly.
Thank you.

One last thing I just noticed that your formula only works well until 2029 and anything after is formatting as 1930, 1931, 1932 and so on =IF(LEFT(B5,2)="17",--TEXTJOIN("/",1,MID(B5,{5,7,3},2)),"")
For instance
17301031101234578 is formatting as 10/31/1930 but
17291031108704217 will show as 10/31/2029.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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
Back
Top