Excel Formula to ID a Date in a Cell that also contains Text

[LiveKaizen]

Columns A and B contain dates. But sometimes, users alsoinput words along with the dates.


For example, Cell A2 could say, “9/13/18” and Cell B2could say, “Tentative 9/16/18”…or really “blah blah blah 9/16/18 blah blah blah.”


I need a formula in Cell C2 to subtract the dates.Obviously, B2-C2 won’t work…so, what will?? Some kind of Find-Wildcard formula to ID the date and then subtract?


This is a file we use throughout the year every year, not just in Sep 2018.

Thank you!

PS. I've learned so much from this website for MANY years; it's exciting to register and post my first question!

 

[RasGhul]

Hi Live,

You could try this next to your date columns;

Book1
	A	B
1	Entry	Output
2	9/13/18	9/13/18
3	Tentative 9/16/18	9/16/18
4	blah blah blah9/16/18blah blah blah.	Sunday, 16 September 2018
Sheet1

Cell Formulas
Range	Formula
B2	=DATE("20"&LEFT(MID(A2,SEARCH("/",A2)+4,7),2),MID(A2,(SEARCH("/",A2)-1),1),MID(A2,(SEARCH("/",A2)+1),2))

 

[LiveKaizen]

Thank you for your response. But the formula isn’tworking for me. It’s resulting in a “#VALUE!”error.

The “Evaluate Formula” box shows the 2^nd laststep as yielding: DATE(“2001”,”9”,”7/”), and then “#VALUE!”

The correct result here should be “9/7/2018” not 9/7/2001.


Input Clarification: the Date could be anywhere within theCell. The date could be in the beginning, at the end or anywhere in the middle(like “Date Words,” “Words Date,” or “Words Date Words”). In this scenario, an additionalfactor is the user entered a typo (extra forward slash) into the input cell,where it reads, “9/7/2018/ order on hold blah blah.”


More help, please?

 

[Peter_SSs]

Welcome to the MrExcel board!

Could you give us more sample data and expected results (say 5-10 rows) so that we can get a better understanding of the variety of text and any variety in the format of the dates in the cells?

My signature block below has help with good ways to provide sample data that we can copy/paste to test with.

 

[RasGhul]

I've used a formula from Excelisfun for extracting numbers only from text and converting to dates but only with helper columns, I agree with Peter we will need to see more examples to test against;

https://www.youtube.com/watch?v=bqsvOygpQWc&feature=youtu.be

Book1
	A	B	C
1	Entry	Extract	Dates
2	9/13/18	91318	13/09/2018
3	Tentative 9/16/18	91618	16/09/2018
4	blah blah blah9/16/18blah blah blah.	91618	16/09/2018
5	%#^%*^$*^$9/16/18(*_(*&_(*&_(*&	91618	16/09/2018
6	fredjoepeter9/16/18//////////////	91618	16/09/2018
7	&(*&^9/13/18	91318	13/09/2018
Sheet1

Cell Formulas
Range	Formula
C2	=DATEVALUE(CONCATENATE(MID(B2,2,2)&"/"&LEFT(B2,1)&"/"&RIGHT(B2,2)))
B2	{=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A2)))-1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

I've used a formula from Excelisfun for extracting numbers only from text and converting to dates..

I think your suggestion may have some major flaws, particularly since it appears users are entering the data. For example, what happens with these?
ABC 9/3/18 DEF
blah 23 blah 23 blah 9/16/18 X

 

[LiveKaizen]

I'm trying to provide sample data with help from Peter's signature block:


[TABLE="width: 159"]
<tbody>[TR]
[TD="width: 68, bgcolor: transparent"]Date Picked
[/TD]
[TD="width: 68, bgcolor: transparent"]Date Delivered
[/TD]
[TD="width: 76, bgcolor: transparent"]Picking
vs Delivery (#Days)

[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]9/14/2018
[/TD]
[TD="width: 68, bgcolor: transparent"]Planned 9/17/2018
[/TD]
[TD="bgcolor: transparent"]#VALUE!
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]9/13/2018
[/TD]
[TD="width: 68, bgcolor: transparent"]9/14/2018
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]9/13/2018
[/TD]
[TD="width: 68, bgcolor: transparent"]9/13/2018
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]On 9/12/2018 Sent to NJ
[/TD]
[TD="width: 68, bgcolor: transparent"]9/13/2018
[/TD]
[TD="bgcolor: transparent"]#VALUE!
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]9/12/2018
[/TD]
[TD="width: 68, bgcolor: transparent"]9/13/2018
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]9/11/2018
[/TD]
[TD="width: 68, bgcolor: transparent"]9/12/2018
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]9/10/2018
[/TD]
[TD="width: 68, bgcolor: transparent"]9/12/2018
[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]9/7/2018/ order was on hold due to payment
[/TD]
[TD="width: 68, bgcolor: transparent"]9/7/2018
[/TD]
[TD="bgcolor: transparent"]#VALUE!
[/TD]
[/TR]
</tbody>[/TABLE]

 

[RasGhul]

I think your suggestion may have some major flaws, particularly since it appears users are entering the data. For example, what happens with these?
ABC 9/3/18 DEF
blah 23 blah 23 blah 9/16/18 X

Absolutely Peter maybe a VBA option then?

 

[Peter_SSs]

I'm trying to provide sample data with help from Peter's signature block:

9/7/2018/ order was on hold due to payment

That last example with the extra "/" does not help, but if looking for a formula solution, try the one shown in C2 below.
Note that my dates are in d/m/y format.

An alternative with a much simpler formula is to invoke a user-defined function. To implement that ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in D2 in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function DayCount(s1 As String, s2 As String) As Long
  With CreateObject("VBScript.RegExp")
    .Pattern = "\d{1,2}\/\d{1,2}\/\d{4}"
    DayCount = DateValue(.Execute(s2)(0)) - DateValue(.Execute(s1)(0))
  End With
End Function

Excel Workbook
	A	B	C	D
1	Date Picked	Date Delivered	#Days	#Days
2	14/09/2018	Planned 17/9/2018	3	3
3	13/09/2018	14/09/2018	1	1
4	13/09/2018	13/09/2018	0	0
5	On 12/9/2018 Sent to NJ	13/09/2018	1	1
6	12/09/2018	13/09/2018	1	1
7	11/09/2018	12/09/2018	1	1
8	10/09/2018	12/09/2018	2	2
9	7/9/2018/ order was on hold due to payment	7/09/2018	0	0
Subtract Dates

 

[Peter_SSs]

Here is another formula solution that is a bit shorter.

Excel Workbook
	A	B	C
1	Date Picked	Date Delivered	#Days
2	14/09/2018	Planned 17/9/2018	3
3	13/09/2018	14/09/2018	1
4	13/09/2018	13/09/2018	0
5	On 12/9/2018 Sent to NJ	13/09/2018	1
6	12/09/2018	13/09/2018	1
7	11/09/2018	12/09/2018	1
8	10/09/2018	12/09/2018	2
9	7/9/2018/ order was on hold due to payment	7/09/2018	0
Subtract Dates