How do I extract multiple date values from variable text string?

[WanttobeExcelNinja]

How do I extract the date values separately from the variable text strings below? I learned how to extract the numbers only from these cells with the following array Ron (Mr. Excel) posted on youtube:

=SUM(MID(0&M14,LARGE(ISNUMBER(--MID(M14,ROW(INDIRECT("1:"&LEN(M14))),1))*ROW(INDIRECT("1:"&LEN(M14))),ROW(INDIRECT("1:"&LEN(M14))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(M14)))-1))

I was trying to find a way to create these date values from the numbers only string the above formula created but could not figure out.

I also tried different formulas to just pull the first date in one cell and pull the second date in a second cell but was running into problems with the variable nature users input this information. I could spend time slicing the data with text to columns and reformatting but I was hoping to discover a formula that would workaround most entry differences. The goal would be to rerun scripts each month and only add new data and have little work reformatting for entry differences.

Any help, suggestions, guidance is much appreciated.


[TABLE="width: 651"]
<tbody>[TR]
[TD]Linedescription[/TD]
[TD]Mr. Excel Numbers only formula[/TD]
[/TR]
[TR]
[TD]Water Escrows 1/1/2015-1/31/15[/TD]
[TD="align: right"]11201513115[/TD]
[/TR]
[TR]
[TD]BROCHI- 2014 Water Escrow Catch-up 1/1/14-8/31/14[/TD]
[TD="align: right"]2014111483114[/TD]
[/TR]
[TR]
[TD]Water Usage 3/31/14 - 6/30/14[/TD]
[TD="align: right"]3311463014[/TD]
[/TR]
[TR]
[TD]Water Usage 09/12/14 - 10/14/14[/TD]
[TD="align: right"]91214101414[/TD]
[/TR]
[TR]
[TD]Water Usage -4/28/14-5/28/14[/TD]
[TD="align: right"]4281452814[/TD]
[/TR]
[TR]
[TD]Water Usage 4/30/14-5/30/14[/TD]
[TD="align: right"]4301453014[/TD]
[/TR]
[TR]
[TD]Water True-up 4/30/14 - 6/30/14[/TD]
[TD="align: right"]4301463014[/TD]
[/TR]
[TR]
[TD]Water True-up 6/30/14-9/2/14[/TD]
[TD="align: right"]630149214[/TD]
[/TR]
[TR]
[TD]Water Usage (07/11/14-08/06/14)[/TD]
[TD="align: right"]71114080614[/TD]
[/TR]
[TR]
[TD]Water Usage 5/23/14-6/25/14[/TD]
[TD="align: right"]5231462514[/TD]
[/TR]
[TR]
[TD]Water Usage 09/11/14-10/10/14[/TD]
[TD="align: right"]91114101014[/TD]
[/TR]
</tbody>[/TABLE]


Kind Regards,

WanttobeexcelNinja

 

[WanttobeExcelNinja]

Hello VBA Geek,

How would I adjust this vba code to turn the 8 digit dates into a 10 digit date and make it assume current century?

Thanks for your time and consideration.

WanttobeExcelNinja

 

[VBA Geek]

have you tried using text function

[COLOR=#333333]=TEXT(--ExtractDate([/COLOR][COLOR=Blue]$A1,COLUMNS([COLOR=Red]$B1:B1[/COLOR])[/COLOR][COLOR=#333333]),"DD/MM/YYYY")[/COLOR]

 

[WanttobeExcelNinja]

That works but when I try to pivot off max end date it returns default 01/0/1900. I tried right align and date formatting but this did not do the trick.

 

[VBA Geek]

That's because TEXT is returning text.
then just use this

[COLOR=#333333][COLOR=#333333]--ExtractDate([/COLOR][/COLOR][COLOR=Blue]$A1,COLUMNS([COLOR=Red]$B1:B1[/COLOR])[/COLOR][COLOR=#333333][COLOR=#333333])[/COLOR][/COLOR]

then custom format dd/mm/yyyy

then your pivot table should work

 

[WanttobeExcelNinja]

Awesome! Thank You. So the double negative makes it read as integer?

 

[VBA Geek]

Yes or as a Long to be precise

 

[WanttobeExcelNinja]

I appreciate your being precise, it will definitely pay dividends for me as I continue to learn.

Thanks again VBA Geek!

Patrick