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

WanttobeExcelNinja

New Member
Joined
Jan 13, 2015
Messages
8
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.


LinedescriptionMr. Excel Numbers only formula
Water Escrows 1/1/2015-1/31/1511201513115
BROCHI- 2014 Water Escrow Catch-up 1/1/14-8/31/142014111483114
Water Usage 3/31/14 - 6/30/143311463014
Water Usage 09/12/14 - 10/14/1491214101414
Water Usage -4/28/14-5/28/144281452814
Water Usage 4/30/14-5/30/144301453014
Water True-up 4/30/14 - 6/30/144301463014
Water True-up 6/30/14-9/2/14630149214
Water Usage (07/11/14-08/06/14)71114080614
Water Usage 5/23/14-6/25/145231462514
Water Usage 09/11/14-10/10/1491114101014

<tbody>
</tbody>


Kind Regards,

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
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
have you tried using text function

Code:
[COLOR=#333333]=TEXT(--ExtractDate([/COLOR][COLOR=Blue]$A1,COLUMNS([COLOR=Red]$B1:B1[/COLOR])[/COLOR][COLOR=#333333]),"DD/MM/YYYY")[/COLOR]
 
Upvote 0
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.
 
Upvote 0
That's because TEXT is returning text.
then just use this
Code:
[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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
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