date formula

[Rodrigues]

Hello All
On column B (file attached) I need to look at the column I and display the date using format: 02-03-16 (ignoring the text before the date).
On column I, have added some variances as isn't always the same.
Many thanks in advance.
R

 

[Khalid NGO]

Hi Rodrigues,

Based on your posted samples, Try:
=IF(ISERROR(FIND("END",I2)),--TRIM(RIGHT(I2,9)),EOMONTH(--TRIM(RIGHT(I2,9)),0))

OR
=IFERROR(MID(I2,SEARCH("??-??-??",I2),8)+0,EOMONTH(--TRIM(SUBSTITUTE(I2,"END","")),0))

Regards,

 

[Khalid NGO]

You may find this useful:
http://chandoo.org/wp/2012/08/17/extract-dates-from-text/

See the comments for various ideas.

 

[bosco_yip]

or,

=IFERROR(0+RIGHT(I2,8),EOMONTH(0+SUBSTITUTE(I2,"END ",),0))

Regards
Bosco

 

[Rodrigues]

Hi Rodrigues,

Based on your posted samples, Try:
=IF(ISERROR(FIND("END",I2)),--TRIM(RIGHT(I2,9)),EOMONTH(--TRIM(RIGHT(I2,9)),0))

OR
=IFERROR(MID(I2,SEARCH("??-??-??",I2),8)+0,EOMONTH(--TRIM(SUBSTITUTE(I2,"END","")),0))

Regards,

Thanks
R