Extracting 2 text dates from a cell into 2 new cells

[taleye]

Hi

I've been away from Excel for a very long time and my attempts at mid right len formulas are getting me nowhere

I have a single cell that contains a string of text "# Date: Oct 1 2017 - Oct 31 2017"

When someone uses the spreadsheet data is dumped into the file and the cell is populated with whatever daterange they may be looking at, I have to create 2 formulas that will take those 2 written dates and have them converted into actual dates so that calculations can be made on the date range itself such as annualising benefit etc.

The issue is getting the two dates noting that the day could be single or double digit and then converting the two text strings into date format!

Can anybody get me started or help me out here please

Thanks!

 

[admiral100]

Hi,

Let's say A1 is the cell that contains a string of text "# Date: Oct 1 2017 - Oct 31 2017"

B1 first date =TRIM(MID(TRIM(MID(A1,8,12)),FIND(" ",TRIM(MID(A1,8,12))),3))&"/"&MONTH((LEFT(TRIM(MID(A1,8,12)),FIND(" ",TRIM(MID(A1,8,12)))-1)&" 0"))&"/"&RIGHT(TRIM(MID(A1,8,12)),4)

C1 second date =TRIM(MID(TRIM(RIGHT(A1,11)),FIND(" ",TRIM(RIGHT(A1,11))),3))&"/"&MONTH((LEFT(TRIM(RIGHT(A1,11)),FIND(" ",TRIM(RIGHT(A1,11)))-1)&" 0"))&"/"&RIGHT(TRIM(RIGHT(A1,11)),4)

 

[palaeontology]

Ok,

a work in progress.

Let's say the text is found in G6, then ...

To extract the first date, use ... =MID(G6,FIND(CHAR(1),SUBSTITUTE(G6," ",CHAR(1),2))+1,(FIND(CHAR(1),SUBSTITUTE(G6," ",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(G6," ",CHAR(1),2)))-1)

To extract the second date, use ... =MID(G6,FIND(CHAR(1),SUBSTITUTE(G6," ",CHAR(1),6))+1,LEN(G6)-FIND(CHAR(1),SUBSTITUTE(G6," ",CHAR(1),6)))

Am currently playing with ideas to allow excel to recognise these as dates but haven't solved that yet.

Kind regards,

Chris

 

[AOB]

Assuming the string is in cell A1

First date :

=DATEVALUE(TRIM(MID($A1,FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),3))+1,FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),4))-FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),3))))&"-"&TRIM(MID($A1,FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),2))+1,FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),2))))&"-"&TRIM(MID($A1,FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),4))+1,FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),4)))))

Second Date :

=DATEVALUE(TRIM(MID($A1,FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),7))+1,FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),8))-FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),7))))&"-"&TRIM(MID($A1,FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),6))+1,FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),7))-FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),6))))&"-"&TRIM(MID($A1,FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),8))+1,LEN($A1)-FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),8)))))

 

[FormR]

And yet another option for you to try which returns real Excel date serial numbers.

This assumes you are using a UK system that expects dates in DD/MM/YYYY format.

Excel 2013/2016
	A	B	C
1	# Date: Oct 1 2017 - Oct 31 2017	01/10/2017	31/10/2017
2	# Date: Oct 10 2017 - Oct 31 2017	10/10/2017	31/10/2017
3	# Date: Oct 1 2017 - Oct 2 2017	01/10/2017	02/10/2017
4	# Date: Oct 10 2017 - Dec 2 2017	10/10/2017	02/12/2017
Sheet1

Cell Formulas
Range	Formula
B1	=0+(MID(A1,13,2)&MID(A1,9,3)&MID(A1,15,5))
C1	=0+(MID(MID(A1,FIND("-",A1)+2,11),5,2)&LEFT(MID(A1,FIND("-",A1)+2,11),3)&RIGHT(MID(A1,FIND("-",A1)+2,11),4))