Let’s say that you wish to extract the dates from a less-than-ideal set of data. For example, the Date Field may be mixed with text, spaces, symbols, or some combination. So, what can you do with this unruly data? Happily, Excel provides many possible solutions!
One way is to create a special formula that scours the field, and returns the date. By using the MID, FIND, and DATEVALUE functions, most any irregular data can be handled with ease. By adding in the IFERROR function, you can also keep your spreadsheet clear of any distracting error messages.
Putting this all together, creates the following formula: =IFERROR(DATEVALUE(MID(B4,FIND( "/", B4 )-2,10)),"") which, as illustrated in the example below, neatly extracts the dates from the unconventional data contained in Column A:
There are, of course, typically numerous ways to achieve any result in Excel, and this is true in this case. It is, however, one nifty little way to extract your dates from many types of unorthodox fields of data that you may have to work with in the future. Try it some time when you need to Find Your Data!