Date Formula From Text in Alternate Cell

[ladylissa21]

Hello smart people!

I'm using a formula currently to extract the date out of a text comment, and it's been working well until we hit the double digit months. It enters the single digit months perfectly, as (for example) 09/13/2018. But in the double digit months, it's giving me (for example, in October) 0/23/2018. It's not recognizing that first digit.

So currently L2 has a text comment that reads something like: SERVICING NOTE REVIEW COMPLETED ON 10/24/2018.

And the existing formula in M2 is: =MID(L2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},L2,1),LEN(L2)+1)),LOOKUP(1,0/MID(L2,ROW(INDIRECT("1:"&LEN(L2))),1),ROW(INDIRECT("1:"&LEN(L2)))) + 1 - MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},L2,1),LEN(L2)+1)))

I found that formula online and have no idea how to alter it to fix the issue. I want it, ideally, to populate the date as given in the note, and ignore any empty fields in L2. Thank you!

 

[jtakw]

Hi,

If your dates within the Text string is Always in the format mm/dd/yyyy

Formula will extract the Date if exist; otherwise, results in Blank:

Book1
	L	M
2	SERVICING NOTE REVIEW COMPLETED ON 10/24/2018	10/24/2018
3
4	SERVICING NOTE REVIEW HAS NOT BEEN COMPLETED
5	SERVICING NOTE REVIEW COMPLETED ON 09/13/2018	09/13/2018
Sheet350

Cell Formulas
Range	Formula
M2	=IFERROR(MID(L2,SEARCH("??/??/????",L2),10),"")

 

[ladylissa21]

That takes care of a lot of it, thank you! Is there any way to account for other date formats in the formula, such as M/D/YY or MM/D/YY?

 

[jtakw]

Will there be Other numbers besides the Date within the text string?

Please show a Few sample of possible text strings.

 

[ladylissa21]

No, there shouldn't be any other numbers. Some additional examples of the text strings are: SERVCING NOTE REVIEW 5/25/18, SERVICING NOTE REVIEW 11/7/2018, or SERVICING NOTE REVIEW 1/1/19. I'm really just asking if there's a way for the formula to account for variations in the date formats; if not, I've still got good old Find and Replace!

 

[jtakw]

Are the Dates Always at the End of the Text string?

Edit: ALSO, do you need the Result converted to Real Date Values in case for further comparison/math?

 

[jtakw]

If the answers to my questions above are YES, NO, respectively, use M2 formula.
If the answers are NO, YES, respectively, use N2 formula and Format result Cell/Column as Date:

Book1
	L	M	N
2	SERVICING NOTE REVIEW COMPLETED ON 10/24/2018	10/24/2018	10/24/2018
3
4	SERVICING NOTE REVIEW HAS NOT BEEN COMPLETED
5	SERVICING NOTE REVIEW COMPLETED ON 09/13/2018	09/13/2018	9/13/2018
6	SERVCING NOTE REVIEW 5/25/18	5/25/18	5/25/2018
7	SERVICING NOTE REVIEW 11/7/2018	11/7/2018	11/7/2018
8	SERVICING NOTE REVIEW 1/1/19	1/1/19	1/1/2019
9	SERVICING NOTE REVIEW ON 09/13/2018 COMPLETED		9/13/2018
10
11			Format Result
12			Column/Cell as Date
Sheet350

Cell Formulas
Range	Formula
M2	=MID(L2,MIN(FIND({0,1,2,3,4}+{0;5},L2&1/17)),99)
N2	=IFERROR(LEFT(SUBSTITUTE(MID(L2,MIN(FIND({0,1,2,3,4}+{0;5},L2&1/17)),99)," ",REPT(" ",99)),99)+0,"")

 

[ladylissa21]

Thank you so much for your help, @jtakw! I'm having a new issue with this same data due to a system change. Now, when the data comes in (same as in your example table above), it often includes a series of underscores at the end, that the system uses to complete the line. The N formula is what I was using, successfully - is there any way to adapt it to this[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]

LATE CHARGE NOTE REVIEW COMPLETED ON 08/28/2018___________________​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[/TR]
[TR]
[TD]

LATE CHARGE NOTE REVIEW COMPLETED ON 09/17/2018___________________​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[/TR]
[TR]
[TD]

LATE CHARGE NOTE REVIEW COMPLETED ON 10/16/2018___________________​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[/TR]
[TR]
[TD]

_LATE CHARGE NOTE REVIEW COMPLETED ON 09/12/2018​

<strike></strike>[/TD]
[TD]

9/12/2018​

[/TD]
[/TR]
[TR]
[TD]

_LATE CHARGE NOTE REVIEW COMPLETED ON 10/29/2018​

<strike></strike>[/TD]
[TD]

10/29/2018​

[/TD]
[/TR]
</tbody>[/TABLE]
new data?

 

[jtakw]

Modified for your current need:

Book1
	A	B
1	LATE CHARGE NOTE REVIEW COMPLETED ON 08/28/2018___________________	8/28/2018
2	LATE CHARGE NOTE REVIEW COMPLETED ON 09/17/2018___________________	9/17/2018
3	LATE CHARGE NOTE REVIEW COMPLETED ON 10/16/2018___________________	10/16/2018
4	_LATE CHARGE NOTE REVIEW COMPLETED ON 09/12/2018	9/12/2018
5	_LATE CHARGE NOTE REVIEW COMPLETED ON 10/29/2018	10/29/2018
6	SERVICING NOTE REVIEW COMPLETED ON 10/24/2018	10/24/2018
7	SERVICING NOTE REVIEW HAS NOT BEEN COMPLETED
8	SERVICING NOTE REVIEW COMPLETED ON 09/13/2018	9/13/2018
9	SERVCING NOTE REVIEW 5/25/18	5/25/2018
10	SERVICING NOTE REVIEW 11/7/2018	11/7/2018
11	SERVICING NOTE REVIEW 1/1/19	1/1/2019
12	SERVICING NOTE REVIEW ON 09/13/2018 COMPLETED	9/13/2018
Sheet350

Cell Formulas
Range	Formula
B1	=IFERROR(SUBSTITUTE(LEFT(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17)),99)," ",REPT(" ",99)),99),"_","")+0,"")

 

[ladylissa21]

That works perfectly, thank you! The only ones not pulling over now are the ones with dates that are really formatted incorrectly, like a five-digit year or something - which works, because I want to correct those for my final results. Thanks again!