How can I extract a date from a string?

Hi All,

Is there a way to extract a date from within a string, when the format and position of the date is inconsistent?

Examples of the dates in a full string are as follows (I've used 'Text' to identify a non date text string/word which can vary in number of characters, though to complicate further the number of words in the full string can also vary):

Text 31-01-2017 Text Text Text
Text 31/01/17 Text Text Text
Text 31.01.17 Text Text Text
Text 31/01/2017 Text Text Text Text
Text Text Text Text 31-01-2017
Text Text Text Text 31/01/17

I can extract the date string & convert to a value if the position (whether by first/last character position or word number) is static or if the date followed a certain character (Using FIND), however this is not the case. Is there any function like "StringContainsADate" or any other way to achieve this?

Thanks in advance

Tooley

Hi Special K & Bebo,

Special K,
Unfortunately the data comes from a free text field input by the end user and I have no way of controlling how it's input.....Though have recommended a change in process so if any date is not input in an acceptable format, then any issues fall back on the user/team. In this case I'm please to inform that Bebo's suggestion does the trick. Thanks for getting back to me

Bebo, Thanks hugely, I've tested about 2 dozen different combinations of date formats & position and so far has worked 100%.

One point for other users, it's an array formula so you must press [Ctrl]+[Shift]+[Enter] before & after typing the formula otherwise you'll get a #VALUE error.

Thanks again