Multiple date formats need to be converted

[foamcows]

I have daily report files from 4 different locations. While I have been successful in getting the locations to use the same standard form, they each have developed their own practice for naming the files with the date. It would be very ideal to have the computer pull the date from the file name rather than having to manually enter it each time. Unfortunately, the only place the date is stored in the file is in the name. An example is below with the plant initials and the format in which they enter the date in the filename.

FUR 07.01.2011
DAY 07.01.11
FAY 07-01-11
SEA 7.1.11

The entire filename is similar to "SEA SUPPLIER DOT 7.1.11.xls"

While it would be easiest to get them to change to a standard formatting from this point forward, I still have years worth of data in the above format that I need to work with.

The one that gives me the biggest headache is SEA. I am not sure how to attack this one as I am unable to do a =MID(...) formula because the location of the month and date will change depending on if it is one or two digits to make the month and date.

Thanks for any help you can offer.

[Domski]

This might work:

=DATEVALUE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,".xls","")," ",REPT(" ",50)),50)),".","-"))

Dom

[foamcows]

holy wow Dom, that works like a champ!! Thanks a bunch.