Extract date from text

[Neokey]

Hi All,

I wonder if anyone can help?

I have a workbook that contains a date in a text cell and I would like to create a function to extract the date, I would also like to format the cell as a date after the information has been extracted.

Oh and I forgot to mention that not all the cells have text so would it be possible to ignore the cell that are blank?

Below is an example of the text in the cell with the date: -

Payment# 328604 on 09/14/18 for GBP 231.00 Thank you for looking and I would be really grateful for any help.

<colgroup><col width="387"></colgroup><tbody>
</tbody>

 

[jtakw]

I tried the formula and even though I formatted the resulting cells as a date I am still missing some dates.
I'm not sure why, but all the tests are only returning the date from certain cells?

The dates in the report are mm/dd/yy, but I suspect that you use dd/mm/yy

Try my formula in Post # 5 again, but format result column Custom dd/mm/yyyy

As Fluff suggested, may be your region uses dd/mm/yyyy format by default?
If this is true, the dates that are Not being shown, for example, the Last entry in your sample in Post # 4, 07/13/18, is being interpreted as Month of 13, 7th, 2018 , which, obviously does not exist.

 

[Fluff]

You can add 2000 to the year like
=IFERROR(DATE(MID(A2,FIND("/",A2)+4,2)+2000,MID(A2,FIND("/",A2)-2,2),MID(A2,FIND("/",A2)+1,2)),"")
@jtakw
Your formula is still giving the wrong result for me. As shown by the OP in Post#4

 

[jtakw]

@jtakw
Your formula is still giving the wrong result for me. As shown by the OP in Post#4

If you're referring to my formula in Post # 5, it works for me...
I'm still thinking it's a regional setting issue for OP...

Book1
	A	B
1	Payment	Formatted as Date
2	Payment# 318310 on 07/13/18 for GBP 5.18	7/13/2018
3	Payment# 322013 on 08/03/18 for GBP 45.50	8/3/2018
4	Payment# 318310 on 07/13/18 for GBP 83.32	7/13/2018
5	Payment# 318310 on 07/13/18 for GBP 58.81	7/13/2018
6	Payment# 318310 on 07/13/18 for GBP 71.18	7/13/2018
7	Payment# 318310 on 07/13/18 for GBP 46.87	7/13/2018
8	Payment# 318310 on 07/13/18 for GBP 34.06	7/13/2018
9	Payment# 318310 on 07/13/18 for GBP 20.82	7/13/2018
10	Payment# 318310 on 07/13/18 for GBP 36.84	7/13/2018
11	Payment# 318310 on 07/13/18 for GBP 85.49	7/13/2018
12	Payment# 318310 on 07/13/18 for GBP 36.84	7/13/2018
13	Payment# 318310 on 07/13/18 for GBP 25.34	7/13/2018
14	Payment# 322013 on 08/03/18 for GBP 49.96	8/3/2018
15	Payment# 318310 on 07/13/18 for GBP 88.55	7/13/2018
16	Payment# 318310 on 07/13/18 for GBP 34.81	7/13/2018
17	Payment# 318310 on 07/13/18 for GBP 11.45	7/13/2018
18	Payment# 318310 on 07/13/18 for GBP 7.56	7/13/2018
19	Payment# 318310 on 07/13/18 for GBP 98.50	7/13/2018
20	Payment# 316703 on 07/06/18 for GBP 226.84	7/6/2018
21	Payment# 316703 on 07/06/18 for GBP 151.57	7/6/2018
22	Payment# 316703 on 07/06/18 for GBP 109.52	7/6/2018
23	Payment# 316703 on 07/06/18 for GBP 117.52	7/6/2018
24	Payment# 316703 on 07/06/18 for GBP 117.52	7/6/2018
25	Payment# 316703 on 07/06/18 for GBP 46.87	7/6/2018
26	Payment# 316703 on 07/06/18 for GBP 151.57	7/6/2018
27	Payment# 316703 on 07/06/18 for GBP 46.87	7/6/2018
28	Payment# 316703 on 07/06/18 for GBP 95.98	7/6/2018
29	Payment# 316703 on 07/06/18 for GBP 199.31	7/6/2018
30	Payment# 316703 on 07/06/18 for GBP 46.87	7/6/2018
31	Payment# 316703 on 07/06/18 for GBP 2.78	7/6/2018
32	Payment# 316703 on 07/06/18 for GBP 6.46	7/6/2018
33	Payment# 318310 on 07/13/18 for GBP 124.20	7/13/2018
Sheet330

Cell Formulas
Range	Formula
B2	=IFERROR(MID(A2,SEARCH("??/??/??",A2),8)+0,"")

Formula copied down.

 

[Fluff]

If you're referring to my formula in Post # 5, it works for me...
I'm still thinking it's a regional setting issue for OP...

I was referring to that formula & I'd agree that it's a regional setting as I get the same result as the OP showed in post#4.

Hence why i said

The dates in the report are mm/dd/yy, but I suspect that you use dd/mm/yy

 

[jtakw]

Try my formula in Post # 5 again, but format result column Custom dd/mm/yyyy

As Fluff suggested, may be your region uses dd/mm/yyyy format by default?
If this is true, the dates that are Not being shown, for example, the Last entry in your sample in Post # 4, 07/13/18, is being interpreted as Month of 13, 7th, 2018[/B] , which, obviously does not exist.

Agree, see quote of Post # 11.

 

[Neokey]

That worked perfectly.

Thank you to everyone that helped to solve the problem.

 

[Neokey]

=IFERROR(DATE(20&MID(A2,FIND("/",A2)+4,2),MID(A2,FIND("/",A2)-2,2),MID(A2,FIND("/",A2)+1,2)),"")

Can't find a date that isn't there, this assumes 2000 by putting 20 in front of the year

This is the formula that worked without any changes to region.