Need a modified formula...

[JLouis]

I got the formula below from the board via a question. Never understood how it worked and now it returns a #VALUE.

=MID(B5,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B5,1),LEN(B5)+1)),LOOKUP(1,0/MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),ROW(INDIRECT("1:"&LEN(B5)))) + 1 - MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B5,1),LEN(B5)+1)))

This formula extracts a date from the following entry: Date: 1/7/2020. The above formula returns 1/7/202 so when it is referenced in another sheet I get the #VALUE error referenced above.

I can use =right(b5,9) to return the correct date, but I'm concerned when the month goes to 2 digits it will not work again.

How do I modify the above formula to return the correct date in all circumstances?

Thanks in advance.

Louis

 

[kweaver]

If "Date: " is before each date, you could use this: =RIGHT(B5,LEN(B5)-FIND(" ",B5))

 

[JLouis]

We were on the same page. I found that =REPLACE(B5,1,5,"") works as well. I'll have to see how it ages. Thank you for the reply.

 

[kweaver]

It depends on what you really want the date to be (text or a date). For example: Date: 01/07/2020 would return 01/07/2020, but if you put a 1* in front of the formula, you'll get 1/7/2020 which you could format as a date in a style of your choosing.

 

[Dave Patton]

Book1
	B	C
4
5	Date: 1/7/2020	01-Jul-20
6	Date: 1/7/20	01-Jul-20
6b

Cell Formulas
Range	Formula
C5:C6	C5	=DATEVALUE(MID(B5,6,10))

 

[JLouis]

Excellent! Thank you.

 

[kweaver]

I suspect the "6" in Dave's formula should be a 7.

 

[Dave Patton]

thanks kweaver

Book1
	B	C	D
5	Date: 1/7/2020	01-Jul-20	01-Jul-20
6	Date: 1/7/20	01-Jul-20	01-Jul-20
7
6b

Cell Formulas
Range	Formula
C5:C6	C5	=DATEVALUE(MID(B5,7,10))
D5:D6	D5	=MID(B5,7,10)+0